Return values in comma separated format when specific text is found in a string

[webarnes99]

I have a list of IDs that I want to put into a single field in a comma separated format based on a condition where specific text is found. I have attached a screenshot of the dataset. I want the mediaIDs to be the coma separated values and I am looking for specific text in the categories column. Any help is very much appreciated.

 

[Scott Huish]

There is no screenshot

 

[webarnes99]

Sorry it was blocked due to size but it worked this time. values to return are in column B and search text is in column E.

 

[Scott Huish]

OK, so what is the condition exactly? Are these values going in the blank Column C? Are you just looking to extract unique numbers out of the text in Column E or what exactly?

 

[webarnes99]

Hi Scott, apologies for not supplying enough info. I attached a new screenshot that hopefully better illustrates what I am trying to do along with a little more context. I have two tabs in the spreadsheet. Tab 1 - Column A contains lookup values I want to search for in Tab 2 - Column E. Everywhere that value is found, I want to have the value in Tab 2 - Column D populate a comma separated list in Tab 1 Column B. I have tried a few suggestions from the web but I am a bit stuck on how to do this when there are a number of values returned and they need to be formatted in a comma separated list.

Really appreciate any help you or anyone can provide.

 

[Cubist]

Try this in B3 only. Clear out all cells below.

=MAP('Tab1'!A3:A8,LAMBDA(m,TEXTJOIN(", ",,(FILTER('Tab2'!D3:D15,ISNUMBER(SEARCH(m,'Tab2'!E3:E15)),"")))))

 

[webarnes99]

Giving that a shot now. It's trying but I have the pinwheel of death on my Mac. Its a large dataset so I am letting it run for a bit.

 

[webarnes99]

I am going to need to move just the data I need to a new SS. The pinwheel of death happens every time I try to run that formula. I will rebuild this in the AM in a new sheet and report back. Thanks for all of the help so far.

 

[Cubist]

If you have a large data set, you should consider using VBA. The FILTER function is pretty taxing on large data sets. Secondly, note that each cell has a limit of ~32,000 characters. This could be a concern considering your data.

 

[webarnes99]

Ok so I rebuilt the dataset with just the data that I need and it looks like that is working a little better but one of the cells is definitely going over the 32,000 character limit. I can remove that value and will figure out a way to do that one manually. One question, in the formula it looks like the map function is looking at the full array but does it need to? I think that can just look for the individual value from cell A3, A4, etc. Is there a reason that needs to be the full array? I have tested it and it seems to be returning correct results with just the single cell reference.

Also thank you to this forum and its great members for the help here, invaluable.

=MAP('Tab1'!A3:A8,LAMBDA(m,TEXTJOIN(", ",,(FILTER('Tab2'!D3:D15,ISNUMBER(SEARCH(m,'Tab2'!E3:E15)),"")))))