REQUIRE HELP IN A COLLECTION

G

gaurav.bajaj

New Member
CONDITION : I HAVE A EXCEL FILE WITH 3 COLUMNS ( AGENT ID, AGENT NAME AND AGENT AMOUNT). FEW AGENTS HAVE MULTIPLE AMOUNT ENTRIES IN IT. WHAT I WANT, IS TO FIND UNIQUE NAME (i.e. NO DUPLICATE NAMES) AND TO CREATE A NEW COLLECTION.

CHALLENGE I AM FACING: I HAVE SUCCESSFULLY SEPARATED UNIQUE NAMES AND HAVE MOVED THEM TO NEW COLLECTION. BUT I AM NOT ABLE TO UNDERSTAND HOW CAN I ADD AMOUNT OF MULTIPLE ENTRIES FOR SAME PERSON TO SECOND COLLECTION.

KINDLY GIVE YOUR INPUTS TO HELP RESOLVE MY QUERY.
 
V

VJR

Well-Known Member
Hi gaurav.bajaj,


Approach #1:
One approach would be to make use of the existing actions available in Blue Prism
The steps would be as below -

Before you remove the duplicates,
- For example your worksheet and your collection looks like the below
1525950081369.png

- Loop on the Collection
- Use 'Filter Collection' action on the Agent Name column.
eg; At first iteration of the loop this will give you only those rows that have Agent Name as ONE.
Now you need to run another "inside loop" to total up the Amounts of the filtered rows and store it in another column of the collection say TOTAL.
- When both the inside and the outside loops are finished the TOTAL column will have all the correct totals and your collection should look like the below.
1525950117145.png
- Delete the collection Agent Amount column using the action in BP (if you want to have this column removed).
- Remove duplicates using the process that you have already developed.
- Now your final collection should look like this
1525950047906.png

Disadvantages of using this approach:
This involves looping twice and would not be advisable if there is large data as it may take longer times to complete the process which is not a best practice.


Approach #2:
Using the Code stage.

- In the Excel file itself add a new column header called TOTAL.
Use the 'Set Cell Value' action of MS Excel VBO
- Create a new action in the MS Excel VBO to drag the desired formula to the entire range.
All the steps and the code that you require is available in Post #5 here.
Assuming your data is arranged in columns A to C,
1525951168048.png

...the formula that you will need is as below-

=SUMIF($B$2:$B$7,B2,$C$2:$C$7)

You need to modify the line of code to set the formula from Range D2 to D LastRow.
Also in the below line instead of 7th row (in B7 and C7) you need to use B LastRow and C LastRow.
"=SUMIF($B$2:$B$7,B2,$C$2:$C$7)"

It will be just one line of code that you will need to modify.

LastRow is a parameter passed from the Process to this new Object.
You will get the LastRow from the 'Get Number Of Rows' action of the MS Excel VBO.

- After running the code, the formula will be applied for the entire column and your Excel would look like this.

(Notice the formula bar for D7)
1525952649865.png

- Get this Worksheet as Collection
- Delete the Agent Amount column from the collection using the action in BP (if you want to have this column removed).
- Run your Remove Duplicates code.
(If you prefer you can make use of Post #2 here)
- Delete the TOTAL column from the sheet OR in the beginning you can also create a temporary copy of the original file and then delete the file.
- Your final collection should look like the below
1525951595778.png
 

Attachments

  • 1525949789697.png
    1525949789697.png
    6.2 KB · Views: 8
G

gaurav.bajaj

New Member
VJR said:
Hi gaurav.bajaj,


Approach #1:
One approach would be to make use of the existing actions available in Blue Prism
The steps would be as below -

Before you remove the duplicates,
- For example your worksheet and your collection looks like the below
View attachment 758

- Loop on the Collection
- Use 'Filter Collection' action on the Agent Name column.
eg; At first iteration of the loop this will give you only those rows that have Agent Name as ONE.
Now you need to run another "inside loop" to total up the Amounts of the filtered rows and store it in another column of the collection say TOTAL.
- When both the inside and the outside loops are finished the TOTAL column will have all the correct totals and your collection should look like the below.
View attachment 759
- Delete the collection Agent Amount column using the action in BP (if you want to have this column removed).
- Remove duplicates using the process that you have already developed.
- Now your final collection should look like this
View attachment 757

Disadvantages of using this approach:
This involves looping twice and would not be advisable if there is large data as it may take longer times to complete the process which is not a best practice.


Approach #2:
Using the Code stage.

- In the Excel file itself add a new column header called TOTAL.
Use the 'Set Cell Value' action of MS Excel VBO
- Create a new action in the MS Excel VBO to drag the desired formula to the entire range.
All the steps and the code that you require is available in Post #5 here.
Assuming your data is arranged in columns A to C,
View attachment 760

...the formula that you will need is as below-

=SUMIF($B$2:$B$7,B2,$C$2:$C$7)

You need to modify the line of code to set the formula from Range D2 to D LastRow.
Also in the below line instead of 7th row (in B7 and C7) you need to use B LastRow and C LastRow.
"=SUMIF($B$2:$B$7,B2,$C$2:$C$7)"

It will be just one line of code that you will need to modify.

LastRow is a parameter passed from the Process to this new Object.
You will get the LastRow from the 'Get Number Of Rows' action of the MS Excel VBO.

- After running the code, the formula will be applied for the entire column and your Excel would look like this.

(Notice the formula bar for D7)
View attachment 763

- Get this Worksheet as Collection
- Delete the Agent Amount column from the collection using the action in BP (if you want to have this column removed).
- Run your Remove Duplicates code.
(If you prefer you can make use of Post #2 here)
- Delete the TOTAL column from the sheet OR in the beginning you can also create a temporary copy of the original file and then delete the file.
- Your final collection should look like the below
View attachment 762
Click to expand...
Thanks VJ for your kind support.
 
G

gaurav.bajaj

New Member
I tried as per your described steps.
But, when I use filter collection it gives error. As I understand I need to use specific name which I have to filter. As you mentioned "One".
According to this, do I have to enter different name every time? or can I make an expression which help me automate sheet in one go to check and filter similar names?
Please suggest. thanks
 
V

VJR

Well-Known Member
gaurav.bajaj said:
I tried as per your described steps.
But, when I use filter collection it gives error. As I understand I need to use specific name which I have to filter. As you mentioned "One".
According to this, do I have to enter different name every time? or can I make an expression which help me automate sheet in one go to check and filter similar names?
Please suggest. thanks
Click to expand...
No you should not be hard coding the name, but using a dynamic expression as below
"AgentName = '" & [Coll1.AgentName] & "'"
1525956054869.png

This gives the Collection Out as

(below is a sample with one column)
1525955710877.png

Now you can loop through this Collection Out, calculate to add up the totals and then when the inside loop is over update the TOTAL column in Main Collection. The next time when the Original Loop will run the same will happen for the next entry.
 
V

VJR

Well-Known Member
If you are having a space in the column name then it is going to throw an error. In that case enclose it within square brackets.
 
You must log in or register to reply here.
Top