Finding Rows in a table that have multiple values.

[ahooper10]

Hey guys,

This is probably a simple fix but it's not been easy to find for me.

I have a group of products that we sell within a set. Example - Set Name 016 contains products HR95, SG66 and so on.

I have this data laid out in a table with the Sku and Set name followed by each individual product SKU within that set.

I am trying to figure out how I can search/filter this data to find rows with multiple SKUS.

For example - I want to find all the rows that contain HR65 AND SG66.

Some of my SKUs are alphanumeric and some are just numeric and also each set contains a different number of SKUs so a simple left to right sort and then filter by column didn't work.

Hope I've explained this okay. Excel isn't my strong point!

 

[JamesCanale]

Something like this might help. Put what you are looking for in L1:

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1												c
2	sku	item1	item2	item3	item4	item5	item6	item7	item8	item9		sku	item1	item2	item3	item4	item5	item6	item7	item8	item9
3	1	a	b	c	d	e	f					1	a	b	c	d	e	f	0	0	0
4	2	g	h									3	c	j	k	0	0	0	0	0	0
5	3	c	j	k								5	c	n	o	p	q	r	s	t	u
6	4	a	l	m
7	5	c	n	o	p	q	r	s	t	u
8	6	v	w	b
Sheet25

Cell Formulas
Range	Formula
L3:U5	L3	=FILTER(A3:J8,(B3:B8=L1)+(C3:C8=L1)+(D3:D8=L1)+(E3:E8=L1)+(F3:F8=L1)+(G3:G8=L1)+(H3:H8=L1)+(I3:I8=L1)+(J3:J8=L1),"")
Dynamic array formulas.

 

[ahooper10]

Something like this might help. Put what you are looking for in L1:

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1												c
2	sku	item1	item2	item3	item4	item5	item6	item7	item8	item9		sku	item1	item2	item3	item4	item5	item6	item7	item8	item9
3	1	a	b	c	d	e	f					1	a	b	c	d	e	f	0	0	0
4	2	g	h									3	c	j	k	0	0	0	0	0	0
5	3	c	j	k								5	c	n	o	p	q	r	s	t	u
6	4	a	l	m
7	5	c	n	o	p	q	r	s	t	u
8	6	v	w	b
Sheet25

Cell Formulas
Range	Formula
L3:U5	L3	=FILTER(A3:J8,(B3:B8=L1)+(C3:C8=L1)+(D3:D8=L1)+(E3:E8=L1)+(F3:F8=L1)+(G3:G8=L1)+(H3:H8=L1)+(I3:I8=L1)+(J3:J8=L1),"")
Dynamic array formulas.

Hey James,

Thanks so much for this.

This formula works perfectly for a single item and is really helpful. However, what if I want to find multiple values.

As an example in your sheet above, what if I want to put C AND J in L1. How would I go about this?

Thanks

Ashley