I want a method to achieve this

I want to achieve following , how can i do it easily

please help

Hi @P-A-J ,
you can try this step
read range to get data table
for each row of data
check id with next id,
if next row is duplicate → assign = blank
can you share your file, I will test


How about the following sample?

dt = dt.AsEnumerable.GroupBy(Function(r) r("id").ToString).OrderBy(Function(g) Cint(g.Key)).SelectMany(Function(g) g.Select(Function(r,i) dt.Clone.LoadDataRow({if(i=0,r(0).ToString,""),r(1).ToString()},False))).CopyToDataTable

Sample20230816-3L.zip (8.4 KB)


1 Like

File.xlsx (8.1 KB)


How about the following?

Sample20230816-3L (2).zip (14.2 KB)


Solution of Mr.YoiChi san is too short
you can try it,
my way is easier to understand but it is logger.

Well, understanding your way is shorter for me in the long run.

What if there are more columns like vehicle_name , how will the query change


If Id column always exists at the first column, the following expression will work even if any number of columns exists.

dt.AsEnumerable.GroupBy(Function(r) r(" id").ToString).OrderBy(Function(g) Cint(g.Key)).SelectMany(Function(g) g.Select(Function(r,i) dt.Clone.LoadDataRow({if(i=0,r(0),"")}.Concat(r.ItemArray.Skip(1)).ToArray,False))).CopyToDataTable

Sample20230816-3L (3).zip (14.3 KB)


for me , after the vehicle_name column , other columns are showing their values one rows below the previous column.

Can you share specific sample as file? It’s no problem if dummy data.

Result1.xlsx (8.8 KB)

Is this input file? Can you also share expected output?

vehicle_name column is fine , other columns have their values shifted down by one row, i just want values for other columns adjacent to their ID’s

Sorry i am making it confusing , this is my data , just convert it such that each id has values of other columns adjacent to it.
Data.xlsx (8.9 KB)


Is the following what you expect?


If so, the following will work.

dt.AsEnumerable.GroupBy(Function(r) r("ID").ToString).OrderBy(Function(g) Cint(g.Key)).Select(Function(g) dt.Clone.LoadDataRow(Enumerable.Range(0,dt.Columns.Count).Select(Function(i) String.Concat(g.Select(Function(r) r(i).ToString))).ToArray,False)).CopyToDataTable

Sample20230816-3L (4).zip (13.9 KB)


Actually the output is slightly different , can you try it for this file
Data.xlsx (8.9 KB)

just convert it such that each id has values of other columns adjacent to it.

Can you also share expected output file?

Yeah output should be like this
Result.xlsx (8.7 KB)

I understand your points.
How about the following?

dt.AsEnumerable.GroupBy(Function(r) r("ID").ToString).OrderBy(Function(g) Cint(g.Key)).Select(Function(g) dt.Clone.LoadDataRow(new object(){g.Key}.Concat(Enumerable.Range(1,dt.Columns.Count-1).Select(Function(i) String.Concat(g.Select(Function(r) r(i).ToString)))).ToArray,False)).CopyToDataTable

Sample20230816-3L (5).zip (15.3 KB)


1 Like