Apr 30 2021 09:54 AM - edited Apr 30 2021 10:03 AM
Hello,
I have a list of terms (also duplicates) in column A and specific codes for each term in column B. Now, I want to make a list contains unique terms and those codes which belong to the terms, using a formula. For example, if "book" repeats in two rows, with codes 32 and 65; I want to make a list with just one "book" in cell A1 and 32, 65 in cell B1. Can you help me, please? Thanks.
Apr 30 2021 10:08 AM
That could be
=TEXTJOIN(", ",TRUE, IF(A:A=E2,B:B,""))
Depends on which Excel you are it could be entered as regular formula or with Ctrl+Shift+Enter. Please check in attached.
And from performance point of view it's better to use reference not on entire columns but on range.
Apr 30 2021 10:32 AM - edited Apr 30 2021 10:46 AM
Dear Sergei Baklan,
Thank you so much for your help. I think TEXTJOIN does not work in MS Excel 2010, unfortunately! Are there any other solutions?
Thanks
Apr 30 2021 10:45 AM
Oops... I guess 2010 works with data model, you may create PivotTable on your range adding the measure as
=IF(ISFILTERED(Range[Terms]), CONCATENATEX(Range, Range[Codes], ", "), "")
(right click on range and add measure
Result is
Apr 30 2021 10:59 AM