CODEWITHSUNDEEP
excelexcel-formula
LLL
LLL

Reputation: 469

Excel - how can I combine rows by ID and put the row with different date as the last column?

I have a table that looks below:

id date item
1 03 /02/2023 Apple
1 03 /01/2023 Orange
1 03 /01/2023 Banana
2 03 /01/2023 Kiwi
2 03 /01/2023 Apple
2 02 /14/2023 Orange

How can I make the same id into one row, and make sure that the item with the different date is on the last column? Like below:

id item1 item2 item3
1 Orange Banana Apple
2 Kiwi Apple Orange

Thanks!

Upvotes: 1

Views: 148

Answers (1)

David Leal
David Leal

Reputation: 6759

You can try the following. Assuming no Excel version constraints as per the tags listed in the question.

=LET(A,A2:A7, B,B2:B7, C,C2:C7, ux,UNIQUE(A), cnts,COUNTIFS(A,A,B,B),
 h,HSTACK("id", "item"&SEQUENCE(,ROWS(A)/ROWS(ux))),
 REDUCE(h,ux,LAMBDA(ac,x,VSTACK(ac,
 HSTACK(x,TOROW(SORTBY(FILTER(C,A=x),FILTER(cnts,A=x),-1)))))))

Here is the output: output

h name represents the header and cnts, the counts of dates per id. We use REDUCE/VSTACK pattern(1) to iterate over unique id column values. On each iteration, we use SORTBY to sort C by cnts in reverse order filtering by id column values (A) equal to x, then we convert the result to a row-array via TOROW and append it to x via HSTACK.

(1) Check my answer to the question: how to transform a table in Excel from vertical to horizontal but with different length.

Upvotes: 1

Related Questions