Excel 2016 - Extract the name that contains all items in a list

Question

Closed. This question needs to be more focused. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65861306/excel-2016-extract-the-name-that-contains-all-items-in-a-list

Answer 1

I added a few items to your list for testing.

   Item3    Name2
   Item3    Name4
   Item3    Name6

The first step is to create a pivot table and add a slicer on Item as below.

Because of the slicer, the columns and rows shown in the pivot table need to be counted. The formula in for counting columns in C12 is =COUNTA(C2:F2)-1. This way when the slicer reduces the number of columns, the result is the number of items.

Similarly, count the rows in C13 using =COUNTA(B3:B10)-1.

Then in cell A3 enter =IF(COUNTIF(OFFSET(C3,0,0,1,$C$12),"")=0,B3,""). This formula returns the name if all items are not blank. Copy this formula down the column.

Next in C14 enter =TEXTJOIN(", ",TRUE,OFFSET(Pivot!A3,0,0,C13,1)). This lists all of the names with all items delimited by a ", ". The OFFSET is there to keep from list Grand Total when there are less than 7 names because of the slicer.

Finally, the formula for H2:H4 is =IF(ROW()-1<=$C$12,INDEX($C$2:$E$2,ROW()-1),""). And in I2 is =IF(H2="","",$C$14). This will then need to be copied to I3:I4.

Below is an example showing the results for Items 2&3.