Excel Automation

[RajsudhapriyaCN]

Review all customers in which the "Transcation Limit" is greater than 0. If greater than 0, copy entire row and paste onto new tab 

Note that there are some customers that are shared.

if (A and B), (E and F) are shared customers

1 A

2 B

5 E

6 F

you need to look at the other customer to determine if "combined" they exceed the line of Transcationlimit.  

For example B shows over credit limit but "combined" with A they are not over the line of  transcation

so this line gets removed from the new tab .  

Customer	Customer Name	Transcation Limit
1	A	-123.00
2	B	877.00
3	C	34.00
4	D	-456.00
5	E	543.00
6	F	76.00
7	G	-23.00
8	H	23.00
9	I	-987.00

My Question here is that how to get Shared customer value and to do filter.

Regards,
Sudha

------------------------------
Rajsudhapriya CN
------------------------------

[harish.mogulluri]

Hi rajsudhapriya,

 Not sure if it is every one.  I feel some of the statements in your questions is contradicting.
When ' A' and 'B' are combined it should be  '754' which is greater than transaction limit but in the statement you mentioned it is not.

Based on the question if I understand shared customers are  combining two customers 
Ex:i will check some thing like A+B,A+C,A+D...  similarly B+C, B+D....  after adding te respectively values if those are greater than transaction limit i would consider as shared customers

How do you want to filter do you want to filter in collection or do you want to filter in excel both can be achieved 
For filtering collection you might need to use filter action in collection manipulation for excel you might need to write code stage if those filter in excel is not present in the default ms excel vbo

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish M
Lead developer
America/New_York TX
------------------------------

[RajsudhapriyaCN]

@Harish M  
Review all customers in which the "Transcation Limit" is greater than 0. If greater than 0, copy entire row and paste onto new tab 
Note that there are some customers that are Partners.

you need to look at the other customer to determine if "combined" they exceed the line of Transcationlimit.  

For example 'G' shows over credit limit but "combined" with 'H' they are not over the line of  transcation

so this line gets removed from the new tab .  

Customer	Customer Name	Amt Over Credit Limit
1001	A	1,234.00
2034	B	43.00
4563	C	765.00
4973	D	(654.00)
5321	E	987.00
7894	F	-45.00
7654	G	765.00
8123	H	-10,876.00
9654	I	765.00

shared customer list

Customer no	Customer name
4563	C
4973	D
7654	G
8123	H

My Question here is that how to filter or get Shared customer from the main sheet how to row the shared customer from the row

can you Kindly help me in this

------------------------------
Rajsudhapriya CN
------------------------------

[Dexter_Whelan_SAS]

how do you know how to pair up the customer transactions? That logic seems unclear here, 

Customer	Customer Name	Amt Over Credit Limit
1001	A	1,234.00
2034	B	43.00
4563	C	765.00
4973	D	(654.00)
5321	E	987.00
7894	F	-45.00
7654	G	765.00
8123	H	-10,876.00
9654	I	765.00

How do you know which letters to combine here to get under the [limit]? Can you combine any two or is it always in pairs like ab, cd, ef, gh etc?

------------------------------
Dexter Whelan
Senior Developer/Mentor
SAS
Europe/London
------------------------------