Mapping ColA to ColB values from Excel sheet

Help Community
,
1

Hi Team, I have a use case to map column A and Column B of excel sheet. Ex: Column A has 3 different values with several occurrences, then I should retrieve all the corresponding column B values. I’ve attached the sample excel file. Can someone please help me to get this resolved?
In this sample I have 5 unique values in ColA, here I need the output to be (420-21345-12: [1234,1235,1236,1237,1238,1239] 420-21345-13: [1240,1241,1242,1243] 420-21345-14: [1244] 420-21345-15: [1245])

PO_Item.xlsx (18.2 KB)

2

we can do it with a groupby

3

Hi @AswinSridhar,

Use below expression in assign activity and assign it to ResDT datatable variable

(From row In InputDT.AsEnumerable
Group row By k1=row("PO").toString.Trim Into grp=Group
Let ItemList="["+String.Join(",", grp.Select(Function (x) x("ItemNumber").toString).toArray)+"]"
Let ra = New Object(){k1,ItemList}
Select ResDT.Rows.Add(ra)).CopyToDataTable