Collecting Unique and Duplicate data in Excel

[Patnaik14]

Hi Team,
My data is in excel, please check the Original Data.png

In the Original table, you can observe that for each PO#, multiple products are there.
Like for PO#456, we have 3 Unique Products (369 (Item:45), 258(Item:13), 147(Item:18),654 (Item:14)).
My requirement is:-

1. In collection-1, I want all the unique products (Considering Product No) with Qty as sum of both Unique & Duplicate lines. Please check the Unique.png file, to understand the requirement.

2. In Collection-2, I want to get the list of Duplicate Values. Please check the Duplicate.png.

 

[nickbell123]

1. An Excel pivot table can do this. There are many guides that can help you with this.
2. In Blue Prism you could store the PO and Product into separate data items while looping through a collection of the table data. If the table data (PO and Product) matches the previous line's values for PO and Product, store the collection row into a separate Duplicate collection using the Copy Rows action in Utility - Collection Manipulation.

 

[Patnaik14]

Thanks Nickbell123 for the response.
Could you please elaborate a bit more.

 

[otonx]

You can run sql query on excel

Example :


SELECT [PO],[Product], sum(Qty) as quantity,sum(`Line Item`) as lineitem
from [Sheet1$]
group by [PO],[Product]





Excel Connection String :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%excelfile%;Extended Properties="Excel 12.0 Xml;HDR=yes";

 

[Patnaik14]

Hi,
Forgot to put a point that, I imported these data from an Invoicing Application.
So we have to work something that is allowed within Blue Prism VBO.
We can't perform some excel operation separately, because 1. Collecting unique values with added qty & 2. Collecting duplicate values, these two operations are advised to do inside the Application.
Please advise.

 

[Patnaik14]

You can run sql query on excel

Example :


SELECT [PO],[Product], sum(Qty) as quantity,sum(`Line Item`) as lineitem
from [Sheet1$]
group by [PO],[Product]





Excel Connection String :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%excelfile%;Extended Properties="Excel 12.0 Xml;HDR=yes";

Thanks Otonx for the response.
I run the first query using BP vbo, but I don't want the line item to be added. It should remain as it is.
For the second one, is there any Action available in Excel VBO to run queries...???
Please let me know.