How to search if there is a match between pivot table results and comma separated list

[mssbass]

Data tab: I have some data with a column called provider which contains multiple provider names separated by a comma. (Some of these names may have a comma within the provider name (e.g. ABC HOME, INC, BEES, INC.)
Pivot tab: I have a pivot table showing me provider's names in a column

I need to flag a new column in my Data tab that says whether or not there is a match between my comma-separated values of provider names to the Pivot tab's pivot table provider name list...

almost like this formula (which is not working): IF(ISNUMBER(SEARCH(Pivot_tab'!$A$5:$A$70,Data_tab!AX2)),"yes""no")

 

[arthurbr]

Wouldn't it be easier to compare the provider names to the values in the data source of your pivot table instead of trying to compare to the pivot table?

 

[Peter_SSs]

provider names separated by a comma. (Some of these names may have a comma within the provider name (e.g. ABC HOME, INC, BEES, INC.)

Also, what would be the logic to decide if a comma is separating two providers or if the comma is within one provider name?

 

[mssbass]

Also, what would be the logic to decide if a comma is separating two providers or if the comma is within one provider name?

Good question - I finally had to do it manually by sorting the comma separated by name and used the find function to find each name in my pivot - took me about an hour but got it out...

 

[mssbass]

Wouldn't it be easier to compare the provider names to the values in the data source of your pivot table instead of trying to compare to the pivot table?

doesn't really matter I guess - the pivot table just grouped the provider names for me so instead of 5000 lines, I got around 30 to lookup

 

[Peter_SSs]

took me about an hour but got it out...

Pity it took so long but thanks for letting us know that it is resolved.