Get no of occurrences in excel and format a column

[Shrutiseth810]

Hi All,

Am new to Blue Prism and don't any knowledge on writing macros.

I have a process in which input is an excel(input.jpg) file with company details (Company Name, Email Address, Postal Address, Bill No, Rejection Reason, etc.)

I have to get the number of occurrences for each Company and change format of column C to display bill nos correctly. Need to create Output.jpg

Manually I have created this by -
1) Sort column B (A-Z),
2) Add column D and use =COUNTIFS(B:B,B2,A:A,A2) and likewise
3) Change format of column C to display full Bill nos-> 2.99028E+11 becomes 299027911095

How can I do this in Blue Prism? Please help!

Thanks,
Shruti

 

[VJR]

Hi Shruti,

Lets try to do it without the macros and using pure Blue Prism (must be happy ).

Process Diagram:
Below is how the process diagram looks like. The diagram is re-arranged to fit within one screenshot. Be noted of the direction of arrows from one stage to another.

View attachment 1533384204409.png


Find Last Row that has data:
Fill up the Input parameters of this action and the output parameter will be the LastRow data item.

View attachment 1533384392298.png

Set column C Format:
You can directly change the format of Column C if you prefer. But if you want to have a separate column E then use 'Copy Paste Worksheet as Range' action and copy Col C to Col E and then apply this format on Column E.
A Cell Format of "0" in Excel implies Format to Number with 0 decimal places.
View attachment 1533384444166.png

Sorting Column B:
For sorting Column B we are getting the worksheet into a collection called as 'WorksheetData' and then sorting the collection on Column No 2 and then pasting back the collection to the worksheet.
View attachment 1533384832004.png

View attachment 1533384855528.png

Add Column D Header:
Set the desired header for Column D.

View attachment 1533384886436.png

Set only first formula:
Set the formula only for the first cell in Column D.
Here $ is used prior to the column alphabet (B) to keep the column constant while dragging the formula. And the row number (2) does not have the $ since that needs to be changed for each row as $B2, $B3, ...etc.
View attachment 1533384945246.png

Apply formula to all rows:
Drag the formula from D2 to its last row by doing a simple copy-paste starting from D3 since D2 already has the formula inserted in the previous step.

View attachment 1533385155702.png

Before:
View attachment 1533385574331.png

After:
View attachment 1533385485200.png

 

[Shrutiseth810]

Hi Shruti,

Lets try to do it without the macros and using pure Blue Prism (must be happy ).

Process Diagram:
Below is how the process diagram looks like. The diagram is re-arranged to fit within one screenshot. Be noted of the direction of arrows from one stage to another.

View attachment 1581


Find Last Row that has data:
Fill up the Input parameters of this action and the output parameter will be the LastRow data item.

View attachment 1582

Set column C Format:
You can directly change the format of Column C if you prefer. But if you want to have a separate column E then use 'Copy Paste Worksheet as Range' action and copy Col C to Col E and then apply this format on Column E.
A Cell Format of "0" in Excel implies Format to Number with 0 decimal places.
View attachment 1583

Sorting Column B:
For sorting Column B we are getting the worksheet into a collection called as 'WorksheetData' and then sorting the collection on Column No 2 and then pasting back the collection to the worksheet.
View attachment 1584

View attachment 1585

Add Column D Header:
Set the desired header for Column D.

View attachment 1586

Set only first formula:
Set the formula only for the first cell in Column D.
Here $ is used prior to the column alphabet (B) to keep the column constant while dragging the formula. And the row number (2) does not have the $ since that needs to be changed for each row as $B2, $B3, ...etc.
View attachment 1587

Apply formula to all rows:
Drag the formula from D2 to its last row by doing a simple copy-paste starting from D3 since D2 already has the formula inserted in the previous step.

View attachment 1588

Before:
View attachment 1591

After:
View attachment 1590

Hi VJR,

Thanks a lot for your quick and detailed response, specially providing the solution involving Blue Prism utilities and not macros
I'm able to do till "Add column D Header". Process is failing while executing Set only 1st Formula. Its giving - Could not execute code stage because exception thrown by code stage: Unable to set the NumberFormat property of the Range class error

I also tried to set the format of column D similar to what you suggested in "Set column C format". But still its giving same error.

Also, not sure why, while reading the excel in collection, column 1 of the excel is Field name in my collection, but 1st row is blank in collection.

 

[VJR]

Hi VJR,

Thanks a lot for your quick and detailed response, specially providing the solution involving Blue Prism utilities and not macros
I'm able to do till "Add column D Header". Process is failing while executing Set only 1st Formula. Its giving - Could not execute code stage because exception thrown by code stage: Unable to set the NumberFormat property of the Range class error

I also tried to set the format of column D similar to what you suggested in "Set column C format". But still its giving same error.

Also, not sure why, while reading the excel in collection, column 1 of the excel is Field name in my collection, but 1st row is blank in collection.

Hi Shruti,

You might be using an existing sheet where you might have entered some data earlier and formatted before which may be causing the issues.
Copy columns A, B and C to a fresh new sheet and try running the process again.

 

[Shrutiseth810]

Hi Shruti,

You might be using an existing sheet where you might have entered some data earlier and formatted before which may be causing the issues.
Copy columns A, B and C to a fresh new sheet and try running the process again.

Hi VJR,

I created new workbook and new worksheet and pasted the collection after sorting. Still am getting same error. Please help!

 

[VJR]

Hi VJR,

I created new workbook and new worksheet and pasted the collection after sorting. Still am getting same error. Please help!

Can you show the properties of the Set 1st formula stage.

 

[Shrutiseth810]

Can you show the properties of the Set 1st formula stage.

Please refer the attachment.

 

[VJR]

Please refer the attachment.

And what is the Format given to the Col Z.

 

[Shrutiseth810]

And what is the Format given to the Col Z.

Please refer the attachment.

 

[VJR]

Please refer the attachment.

Ah, gotchya...'Set only 1st formula' should make use of 'Set cell value' instead of 'Format cell' action.

 

[Shrutiseth810]

Ah, gotchya...'Set only 1st formula' should make use of 'Set cell value' instead of 'Format cell' action.

Thanks a ton! Process is working as per your suggestion and output is as I wanted. Attaching the correct action used.