Formula to Determine which Rows share ALL of a list of common values

[mnty89]

I think I am overlooking something incredibly simple. Whether this is just reformatting the data in a specific pivot table or adding in a logic step with a formula on the rows, but I can't currently figure out how to do it.

Here is the issue. I have a data set with quite a few lines. There are essentially two aspects to the data. One is the location, the second is the item. Each location has a row for however many items it carries. I want to separate this data into only locations that carry a certain list of items. I need to "filter out" from the list of locations to keep only the locations which carry the list of items I want.

For example: I have locations 1-10, and items 1-5. I want to find all locations that have all items 1&2. The challenging thing is, the locations that remain need to have ALL of the items I want to select, not just one or some of them. Any ideas on what I'm missing that is totally obvious? The hard part is that there are a few hundred items and a few thousand locations...

 

[sheetspread]

Write an iserror(vlookup) formula that searches the table of items, then filter out the errors.

 

[sheetspread]

Oh wait you want only those locations that match every item in the wish list, hang on.........

 

[oldbrewer]

or example: I have locations 1-10, and items 1-5. I want to find all locations that have all items 1&2. The challenging thing is, the locations that remain need to have ALL of the items I want to select, not just one or some of them.
		location	items
		loc1	135
		loc2	124	7	####
		loc3	145
		loc4	234
		loc5	235
		loc6	345
		loc7	125	12
		loc8	123	13
		loc9	245
		loc10	124	15
	###	by using this formula the row number of locations
		with both item 1 and 2 are given
		=IF(ISERROR(VALUE(SEARCH("1",D7))*VALUE(SEARCH("2",D7))),"",ROW())
		you can filter or pivot or use offset match to get a list of locations
		1	7	loc2	^^^^^^
		2	12	loc7				^^^^^
		3	13	loc8				=IF(D28="","",OFFSET($E$5,MATCH(D28,$E$6:$E$15,0),-2))
		4	15	loc10
		5						the SMALL function is used to pull the matching rows
		6
		7
		8
		9
		10

<colgroup><col><col span="15"></colgroup><tbody>
</tbody>

 

[sheetspread]

This works too:

Excel 2010
	A	B	C	D	E	F
1	Location	Item	Matches List?			List
2	T	ZZ	TRUE			CC
3	W	ZZ	FALSE			XX
4	E	XX	TRUE			ZZ
5	R	CC	TRUE
6	T	CC	TRUE
7	Y	VV	FALSE
8	Q	VV	FALSE
9	Q	VV	FALSE
10	E	ZZ	TRUE
11	T	XX	TRUE
12	R	XX	TRUE
13	E	CC	TRUE
14	W	ZZ	FALSE
15	Q	ZZ	FALSE
16	Y	XX	FALSE
17	U	XX	FALSE
18	Y	ZZ	FALSE
19	T	CC	TRUE
20	R	ZZ	TRUE
Sheet2

Cell Formulas
Range	Formula
C2	{=COUNT(MATCH($F$2:$F$4,IF($A$2:$A$20=A2,$B$2:$B$20),0))=COUNTA($F$2:$F$4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

And you can filter the falses