topic RE: Excel Automation in University Forum

Excel Automation

RajsudhapriyaCN — Thu, 11 May 2023 11:24:00 GMT

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
------------------------------

RE: Excel Automation

harish.mogulluri — Thu, 11 May 2023 13:14:00 GMT

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
------------------------------

RE: Excel Automation

RajsudhapriyaCN — Tue, 16 May 2023 08:00:00 GMT

@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
------------------------------

RE: Excel Automation

Dexter_Whelan_SAS — Tue, 16 May 2023 12:53:00 GMT

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
------------------------------