Excel Which Products Contain Both Of These Items - Episode 2661

[@johndurran614]

This problem would be an ideal opportunity to restart Duelling Excel with Mike Girvin

[@cherianiype]

oh yes! BRING BACK "EXCEL IS FUN" MIKE GIRVIN's DUELING EXCEL! BTW - LOOKING GOOD BILL! TAKE CARE

[@BradleyDunlap]

You are looking great Bill!

[@KO1967]

Looking good Bill. Keep up the good work. I don't think the challenge in this is the formula as much as it is the premises/assumptions and data. The following formula will return a comma separated list of WOIDs from the FullCatelog based on the songs in each WOID within the QHList (note that I converted to tables for simplicity). =LET(vSongs,FILTER(HSTACK(tblQHCatalog[Artist | Song],tblQHCatalog[Coach],tblQHCatalog[Intensity]),tblQHCatalog[WOID]=[@WOID]), vWOID1,FILTER(tblFullCatalog[WOID],(tblFullCatalog[Artist | Song]=INDEX(vSongs,1,1)),""), vWOID2,FILTER(tblFullCatalog[WOID],(tblFullCatalog[Artist | Song]=INDEX(vSongs,2,1)),""), vBoth,FILTER(vWOID1,ISNUMBER(XMATCH(vWOID1,vWOID2))), IFERROR(ARRAYTOTEXT(SORT(vBoth)),"")) To better evaluate this I also created 3 other columns (QH Song/Coach, QH Song/Intensity, QH Song/Coach/Intensity) where each just alters the above formula to include additional filters (adding Coach, adding Intensity, adding Coach and Intensity). Then if you wanted you could create one additional column whereby you could search for "," (comma) in each column descending in filter complexity and if no comma found and not blank then pull in that column's value (i.e. if no comma in QH Song/Coach/Complexity and not blank) then use this value else check Song/Intensity else check Song/Coach, else take TEXTBEFORE comma of QH Song column. That said based on your data you have some QH WOIDs that have no matches (e.g. 497) and some that have multiple matches in more than one variation of the filter/column (e.g. 652 is found in 277 and 285 when filtering by just song and by Song/Intensity with no match on Song/Coach). I'd be happy to email you the workbook if you like but my analysis indicates there is no absolute solution to your problem as the data does not support the theory that each QH is in a subset of ONE of the longer workouts.

[@AlThePal78]

glad you are doing better

[@Excelambda]

Great challenge,!! 2 pivotby arrays mapped with a search "connector" can do the trick

[@GeertDelmulle]

Hi Mr.Excel, This challenge is easily solved in Power Query. (You just KNEW I was going that route, right? ;-) I sent you a file (I removed all the stuff I don't need). Please let me know what you think. NB: the results are what they are: multiple matches, no matches,...

[@JustinAnderson1]

I think this could do what you need, to find the duplicates in K4 and L4: =ARRAYTOTEXT(LET(x,TRIM(TEXTSPLIT(K4, ",")),y,TRIM(TEXTSPLIT(L4, ",")),FILTER(x,NOT(ISERROR(MATCH(x,y,0))))))