1

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!

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
LLL
  • 419
  • 2
  • 6
  • 17
  • Can you guarantee there will be only 3 IDs and only one date will differ? And it is undefined if it would be greater or lower than the other two? – Horaciux Apr 27 '23 at 19:55
  • What version of Excel? – Scott Craner Apr 27 '23 at 20:06
  • @Horaciux Yes there are only 3 IDs and only one date will differ. It's not guaranteed if it would be greater or lower than the other two – LLL Apr 27 '23 at 20:46

1 Answers1

1

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.

David Leal
  • 6,373
  • 4
  • 29
  • 56