I want a method to achieve this

Help IT Automation
, , , ,
1

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

image
image833×566 21 KB

please help

2

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
regards,
LNV

3

Hi,

How about the following sample?

image
image779×543 19.1 KB

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)

Regards,

1 Like
4

File.xlsx (8.1 KB)

5

Hi,

How about the following?

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

Regards,

6

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

7

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

8

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

9

HI,

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

image
image852×535 19.4 KB

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

Regards,

10

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

11

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

12

Result1.xlsx (8.8 KB)

13

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

14

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

15

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)

16

Hi,

Is the following what you expect?

image

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

image
image758×526 20 KB

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

Regards,

17

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.

18

Can you also share expected output file?

19

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

20

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)

Regards,

1 Like