Solved MS EXCEL VBO sum of values with same unique label

[shanbhsh]

Hi

I have excel file that has primary key of customer id and other columns. I need to add the dollar amount for each unique customer id using Blue prism.
I would like to get sum of dollar amount for each unique customer id. For customer id : 101, dollar amount should be (200+100+300) = 600
Similarly for customer 201, it should be (200+50+ 200)= 450 etc. The input file is excel as specified and i need to store the sum of dollar amount using Blue prism.
Any help would be appreciated. Do i need to use loop stage or using code stage?
Example:
S no Customer ID Fname Lname Dollar amount Memo Transaction no
1 101 John Doe 200 1 501
2 101 John Doe 100 1 502
3 101 John Doe 300 1 503
4 201 Sam Doe 200 1 201
5 201 Sam Doe 50 1 241
6 201 Sam Doe 200 1 233
7 241 Liz Simmon 50 0 801
8 241 Liz Simmon 50 0 801
9 251 Charlie Jones 10 6 751
10 251 Charlie Jones 20 7 752

 

[cs.andras]

Hi,
I would use a code stage and write a simple code to collect the unique names (E.g.: ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo), then loop on that to create a SUMIF function in each line using the corresponding name.
How does that sound? (Any kind of looping would be much slower than this solution imo.)
NOTE: the code is directly from Excel VBA, might need rewriting for BP code stage!

 

[RDawson9]

Hi @shanbhsh

This is achievable via both a code stage or a loop stage.

The decision about to whether to use a code stage or not should be based on the ability of you and your team. For example, if you have a team of 5 and you are the only one who knows how to code, it's probably not a good idea to use a code stage because, if you were to leave the company for whatever reason, the rest of your team wouldn't be able to support or understand your code.

Could you advise where you need to store the total dollar amount? For example, John Doe would be 200 + 100 + 300. Where should Blue Prism store the 600 value?

Rob

 

[shanbhsh]

Thanks RDawson9 for your response. I would prefer to use Loop stage based on your suggestion. In the above example, since I would givel MS Excel input with the above values, I will add the value for John Doe(200+100+300)= 600 and place it in collection. The 600 value needs to be sent by login to different application and input the value of 600 to some specific field on that screen. The same would be required for 1000's of records that will be present in excel sheet similiar to example provided. Would really appreciate if you can provide some kind of snippet on how the above solution can be achieved.

 

[shanbhsh]

Thanks @cs.andras for response. I will try to use Loop stage and if it doesnt work, then go for the Code stage.

 

[anisjolly]

Hi @shanbhsh Do let us know how you get on!. Also, if you could possibly share the solution so when others with the same problem read the thread, they have something to work from.

Thanks

 

[shanbhsh]

Hi @anisjolly .. I am still not able to figure out on how to resolve this using multiple collections and Loop stage. Any help would be appreciated

 

[anisjolly]

It's quite simple really. The following images should help. I've tested this myself and all the calculations seem to be correct. Let me know once you've solved this so I can mark this thread as solved.

 

[shanbhsh]

Thanks a lot !! @anisjolly . Will give it try and get back to you

 

[shanbhsh]

Thanks @anisjolly. It worked perfectly as required..It was huge help