How to find a missing piece of data from large volume of rows

[Deelof]

How do I fid the variant codes in my mass data that don't an Image Description no 2?
I.e. ST00003-1 has an Image Description of 2 but the other 2 variants don't.

Variant code	Variant description	Image description
ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	1
ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	2
ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	0
ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	4
TB00022-17	PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	1
TB00022-17	PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	0
TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	3
TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	1
TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	0
TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	4

 

[Sam_D_Ben]

Please check, I am not sure if this is what you are looking for,

sdgsagsdgggsfdg.xlsx
	E
1	Variant code <>2
2	TB00022-17
3	TB00022-3
4	0
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER(UNIQUE(A2:A1000), COUNTIFS(A2:A1000, UNIQUE(A2:A1000), C2:C1000, 2)=0)
Dynamic array formulas.

 

[Cubist]

In 2019, you can try adding a helper column "Has 2?" and filter the variant code using pivot table.

Book1
	A	B	C	D	E	F	G
1	Variant code	Variant description	Image description	Has 2?		Has 2?	0
2	ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	1	1
3	ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	2	1		Row Labels
4	ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	0	1		TB00022-17
5	ST00003-1	TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 450	4	1		TB00022-3
6	TB00022-17	PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	1	0		Grand Total
7	TB00022-17	PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	0	0
8	TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	3	0
9	TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	1	0
10	TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	0	0
11	TB00022-3	PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 800	4	0
Sheet6

Cell Formulas
Range	Formula
D2:D11	D2	=COUNTIFS(A:A,A2,C:C,2)

 

[Deelof]

Please check, I am not sure if this is what you are looking for,

sdgsagsdgggsfdg.xlsx
	E
1	Variant code <>2
2	TB00022-17
3	TB00022-3
4	0
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER(UNIQUE(A2:A1000), COUNTIFS(A2:A1000, UNIQUE(A2:A1000), C2:C1000, 2)=0)
Dynamic array formulas.

Thanks but not quite what I was looking for.
Ideally I need in a new column a match to say the variant code has an image description 2 or not

Variant code	Variant description	Image description	RESULT
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	NO 2
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	NO 2
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	6	NO 2
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	7	NO 2
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	5	NO 2
BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	NO 2
BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	2	YES 2
BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	YES 2
BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	YES 2
BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	YES 2
BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	3	NO 2
BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	1	NO 2
BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	0	NO 2
BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	5	NO 2
BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	4	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	3	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	1	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	0	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	6	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	5	NO 2
BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	4	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	0	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	6	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	5	NO 2
BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	NO 2
BT00008-6	ASPEN BEDSIDE / OAK / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400		BLANK
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	3	YES 2
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	1	YES 2
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	2	YES 2
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	0	YES 2
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	5	YES 2
BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	4	YES 2
BT00010-1	BAMFORD BEDSIDE TABLE / OAK		BLANK
BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	3	YES 2
BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	1	YES 2
BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	2	YES 2
BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	0	YES 2

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Variant code	Variant description	Image description	RESULT
2	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	No 2
3	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	No 2
4	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	6	No 2
5	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	7	No 2
6	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	5	No 2
7	BT00008-1	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	No 2
8	BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	2	yes 2
9	BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	yes 2
10	BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	yes 2
11	BT00008-2	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	yes 2
12	BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	3	No 2
13	BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	1	No 2
14	BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	0	No 2
15	BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	5	No 2
16	BT00008-3	ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	4	No 2
17	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	3	No 2
18	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	1	No 2
19	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	0	No 2
20	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	6	No 2
21	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	5	No 2
22	BT00008-4	ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME	4	No 2
23	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	3	No 2
24	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	1	No 2
25	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	0	No 2
26	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	6	No 2
27	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	5	No 2
28	BT00008-5	ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400	4	No 2
29	BT00008-6	ASPEN BEDSIDE / OAK / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400		Blank
30	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	3	yes 2
31	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	1	yes 2
32	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	2	yes 2
33	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	0	yes 2
34	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	5	yes 2
35	BT00009-1	HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 400	4	yes 2
36	BT00010-1	BAMFORD BEDSIDE TABLE / OAK		Blank
37	BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	3	yes 2
38	BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	1	yes 2
39	BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	2	yes 2
40	BT00012-1	BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 416	0	yes 2
Data

Cell Formulas
Range	Formula
D2:D40	D2	=IF(C2="","Blank",IF(COUNTIFS(A:A,A2,C:C,2),"yes 2","No 2"))

 

[Sam_D_Ben]

Thanks but not quite what I was looking for.
Ideally I need in a new column a match to say the variant code has an image description 2 or not

Thank You!