Excel automation based on column values

HI Guys,

Would like to do som excel automation, I have input values in one column as like below:
image

and I need output as follows in another sheet or in another excel:
image

Please help me out on this, thank you.

@gokulvasant, can you share the excel file?

Hi @gokulvasant

You can use the below activity to transpose the data from rows to columns…
https://connect.uipath.com/marketplace/components/data-manipulations

It also contains a user guide where you can refer and understand how to use it :slight_smile:

@gokulvasant
I prototyped with following reduced sample data:
grafik

Stage 1: Finding NSF Name Indexes:
we can do with the help of Enumerable:

(From i In Enumerable.Range(0, dtData.Rows.Count).ToList
Where dtData.Rows(i)(0).toString.StartsWith("NSG")
Select i).ToArray
  • Creating a sequence starting by 0 with the length of datarows count (0,1,2,3…)
  • use the index within a loop and filter out Values starting with NSF
  • project the index into an int array - Result: 0,3

Stage 2: Parse the datatable
Based on the found indexes the blocks can be parsed with Skip and Take method
the Barcode Lines will be added to a intermediate DataTable:
grafik

Stage 3: Group By LOC and Barcode the group Counts will be taken and added to the Result Table:

(From d In dt2.AsEnumerable
Group d By k1=d(0).toString.Trim, k2=d(1).toString.Trim Into grp=Group
let ra = New Object() {k1,k2,grp.Count}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Final Result:
[Column1,BARCODE,QTY
NSGFR1G1A1,12345,2
NSGFR1G1A2,54321,2
NSGFR1G1A2,12345,1

For fast Prototyping reasons LINQ was used. But also these Parts can be decomposed into more essential activities.

Find Starter Help here:
gokulvasant.xaml (11.4 KB)

1 Like