I have a table that has ID and Description. One ID can have multiple rows of Description. I would like to change the format to a table where there will be 1 unique row for each ID and multiple columns for description. Is there a way to do this for a large data set using a formula in excel? Below is a screenshot of current state and desired output.
0
Using DROP(REDUCE()):
=LET(
rng,A3:B12,
unq,UNIQUE(TAKE(rng,,1)),
IFERROR(DROP(REDUCE("",unq,LAMBDA(_z,_y,VSTACK(_z,HSTACK(_y,TOROW(FILTER(TAKE(rng,,-1),TAKE(rng,,1)=_y)))))),1),""))
There is another way of solving this problem with the help of Pivot table.
Create a pivot of your table and add ‘ID’ to Rows field and ‘Description’ to both Columns and Values field.
Your pivot table should look something like this:
After that, add this formula =IF(B3=1,B$2,"") in cell ‘G3’ and drag the formula.
The whole solution will look something like this: