Reputation: 129
I'm trying to concatenate multiples values in one cell based on a lookup search (ID), the thing is this ID sometimes might be alone but other time might be between multiple IDs in the same cell (at the beginning, middle or end separated by commas). I've been using the below formula but only returns the first value when the ID is alone.
Current formula --> =TEXTJOIN(",",TRUE,IFERROR(XLOOKUP(A2,D:D,E:E),"ID not found"))
Hope you can help. Thanks.
Rows highlighted in blue, yellow and green are the expected results (I did them manually). Row 7 is the actual result (wrong/incomplete) for the current formula.
Upvotes: 2
Views: 2038
Reputation: 103
This can also be done via power query. I used different ID values from your table on the right, but it should still work correctly.
Power Query Steps
Change type of Col: Value to be Returned to text
Split Col: ID by Delimeter = ','
You know should have multiple ID columns (ID.X). Select all ID columns > Right click on header > select Unpivot Other Columns
Remove any unecessary columns
Select the column with all of your ID's > right click and select Group By
Under new column name, enter a new column header. Change the Operation to Sum. For Column, select your column that contains your Values to be Returned
Reference this guide for the next step. You need to manually configure the M code in the formula bar & change the formula from List.Sum([COL]).. to Text.Combine([COL], ",")..
The last step is to make sure that your new column is a text column, not a number.
I've attached a copy of my workbook, which should hopefully help. If not, I've pasted my code from the Advanced Editor below in case that is helpful. Be sure to update Table & Column names accordingly based on your workbook.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Value to be returned", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", type text}, {"ID.2", type text}, {"ID.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value to be returned"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Value to be returned"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Value"}, {{"Value.1", each Text.Combine([Value to be returned], ", "), type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Value.1", type text}})
IN
#"Changed Type 2"
Let me know if this works or if you have any follow up questions.
Upvotes: 0
Reputation: 5902
You can try below approach to get results:
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(A2,D:D,1)),E:E,""))
One suggestion would be to limit the entire column usage to improve formula speed.
Upvotes: 2