Forum Discussion
combine data
I wish to combine rows of data linking unique email addresses to all the row/seat numbers associated with that address. (Note: I separately combined the row and seat columns with a formula, but I am not including that column to simplify the task I need help with.)
I have attached a file with an example of the starting table and the table with the result I want.
Thanks for any help.
Randy
- Thanks so much Oliver. Copying the data and pasting it in the blue table and refreshing the green table works great!
=REDUCE(A1:G1,UNIQUE(A2:A9),
LAMBDA(u,v,
VSTACK(u,
HSTACK(
v,
INDEX(B2:F9,XMATCH(v,A2:A9),),
TEXTJOIN(" ",,FILTER(G2:G9,A2:A9=v))))))
=LET(rng,A1:G9,
mails,DROP(CHOOSECOLS(rng,1),1),
rowseats,DROP(CHOOSECOLS(rng,7),1),
data,DROP(CHOOSECOLS(rng,2,3,4,5,6),1),
REDUCE(CHOOSEROWS(rng,1),UNIQUE(mails),
LAMBDA(u,v,
VSTACK(u,
HSTACK(v,
INDEX(data,XMATCH(v,mails),),
TEXTJOIN(" ",,FILTER(rowseats,mails=v)))))))
With Office 365 or Excel for the web you can apply these formulas.
- Oliver, thanks so much for your response and work. I can see from your result that your formula works great. I apologize for my ignorance (I am quite old), but I do not know how to apply your formula to the table using Excel on my computer. I have a new version of Excel on the computer and do not use Excel for the web. It would be great to know the steps to use to apply your formulas to Excel on my computer. Thanks so much for your time.
Randy, you are welcome. I've just read your reply from your first discussion where you say that you're working with Excel 2016. If you still work with Excel 2016 then the suggested formulas don't work because functions such as LET and LAMBDA are only supported in the latest versions of Excel which are Office 365 and Excel for the web.
Alternatively you can use Power Query with Excel 2016. In the attached file you can add data to the blue dynamic table. Actually you can copy all your data and paste it in the blue dynamic table of the sample file. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- Hi peiyezhu. Thanks for yur solution. Someone else sent a solution that works, but your solution may be easier to do. Could you please send me instrutions for each step of the process using your formula? Also, what does the "limit 20" do. I have tables that can have up to about 130 rows.
