GoogleSheets: Screen a Column for IDs and return associated cells (multiple occurrences)

Question

Essentially I have a data-structure like this (hundreds of rows, constantly updated):

ID	Date	Value
1000	date1	value 1
1001	date2	value 2
1002	date3	value 3
1001	date4	value 4
1003	date5	value 5

where

• IDs are unique but can occur multiple times
• Dates indicate when IDs were created or their values updated
• Values are not unique

What I want to do is to search the ID row and get the date&value associated with it. IF the same ID occurs again, it should be listed in the same row, e.g.

ID	Date	Value
1000	date1	value 1
1001	date2	value 2	date 4	value 4
1002	date3	value 3
1003	date5	value 5

Ideally, this is done using an array. Like I said, the list is constantly updated so dragging a formula like:

=TRANSPOSE(FILTER($A$2:$A, $B$2:$B = A2))

isn't really an option...

Any thoughts? Help would be greatly appreciated!!

Cheers

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&"×"&
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), {A2:A&"×"&
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))-1, B2:C}, {2, 3}, 0)))

---

update:

=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 IF(A2:A="",,{A2:A&"×", TO_TEXT(B2:B)&"×"&C2:C&"¤×", ROW(A2:A)&TO_TEXT(B2:B)&"×"&C2:C&"¤×"}), 
 "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3"), 
 "offset 1", 0)),,9^9)), "×")), "¤", ))

demo spreadsheet