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

Deelof

New Member
Joined
Aug 30, 2023
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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 codeVariant descriptionImage description
ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 4501
ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 4502
ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 4500
ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 4504
TB00022-17PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8001
TB00022-17PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8000
TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8003
TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8001
TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8000
TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 8004
 
Please check, I am not sure if this is what you are looking for,
sdgsagsdgggsfdg.xlsx
E
1Variant code <>2
2TB00022-17
3TB00022-3
40
Sheet4
Cell Formulas
RangeFormula
E2:E4E2=FILTER(UNIQUE(A2:A1000), COUNTIFS(A2:A1000, UNIQUE(A2:A1000), C2:C1000, 2)=0)
Dynamic array formulas.
 
Upvote 0
In 2019, you can try adding a helper column "Has 2?" and filter the variant code using pivot table.
Book1
ABCDEFG
1Variant codeVariant descriptionImage descriptionHas 2?Has 2?0
2ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 45011
3ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 45021Row Labels
4ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 45001TB00022-17
5ST00003-1TERRA SIDE TABLE / WHITE / GLOSS / WHITE METAL FRAME / ROUND / H 450 DIA 45041TB00022-3
6TB00022-17PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80010Grand Total
7TB00022-17PERTH DINING TABLE / OAK / WOOD EFFECT / WHITE WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80000
8TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80030
9TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80010
10TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80000
11TB00022-3PERTH DINING TABLE / WHITE / WOOD EFFECT / OAK WOOD LEGS / RECTANGULAR - EXTENDABLE / UP TO 8 SEATS / W 1200 - 1600 / H 770 D 80040
Sheet6
Cell Formulas
RangeFormula
D2:D11D2=COUNTIFS(A:A,A2,C:C,2)
 

Attachments

  • Screen Shot 2025-02-17 at 9.19.24 AM.png
    Screen Shot 2025-02-17 at 9.19.24 AM.png
    82.6 KB · Views: 1
Upvote 0
Please check, I am not sure if this is what you are looking for,
sdgsagsdgggsfdg.xlsx
E
1Variant code <>2
2TB00022-17
3TB00022-3
40
Sheet4
Cell Formulas
RangeFormula
E2:E4E2=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 codeVariant descriptionImage descriptionRESULT
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003NO 2
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001NO 2
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4006NO 2
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4007NO 2
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4005NO 2
BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004NO 2
BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4002YES 2
BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001YES 2
BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004YES 2
BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003YES 2
BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME3NO 2
BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME1NO 2
BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME0NO 2
BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME5NO 2
BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME4NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME3NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME1NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME0NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME6NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME5NO 2
BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME4NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4000NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4006NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4005NO 2
BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004NO 2
BT00008-6ASPEN BEDSIDE / OAK / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 BLANK
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4003YES 2
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4001YES 2
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4002YES 2
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4000YES 2
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4005YES 2
BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4004YES 2
BT00010-1BAMFORD BEDSIDE TABLE / OAK BLANK
BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4163YES 2
BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4161YES 2
BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4162YES 2
BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4160YES 2
 
Upvote 0
How about
Fluff.xlsm
ABCD
1Variant codeVariant descriptionImage descriptionRESULT
2BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003No 2
3BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001No 2
4BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4006No 2
5BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4007No 2
6BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4005No 2
7BT00008-1ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004No 2
8BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4002yes 2
9BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001yes 2
10BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004yes 2
11BT00008-2ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003yes 2
12BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME3No 2
13BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME1No 2
14BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME0No 2
15BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME5No 2
16BT00008-3ASPEN BEDSIDE / OAK / WOOD VENEER / BLACK METAL FRAME / GOLD METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME4No 2
17BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME3No 2
18BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME1No 2
19BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME0No 2
20BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME6No 2
21BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME5No 2
22BT00008-4ASPEN BEDSIDE / WALNUT / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400 / NEW FRAME4No 2
23BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4003No 2
24BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4001No 2
25BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4000No 2
26BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4006No 2
27BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4005No 2
28BT00008-5ASPEN BEDSIDE / WALNUT / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 4004No 2
29BT00008-6ASPEN BEDSIDE / OAK / WOOD EFFECT / BLACK METAL FRAME / BLACK METAL HANDLE / 2 DRAWER / H 505 W 600 D 400Blank
30BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4003yes 2
31BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4001yes 2
32BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4002yes 2
33BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4000yes 2
34BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4005yes 2
35BT00009-1HANNA BEDSIDE / BLACK / OAK / WOOD VENEER / BLACK METAL FRAME / BLACK METAL HANDLE / 1 DRAWER / H 500 W 400 D 4004yes 2
36BT00010-1BAMFORD BEDSIDE TABLE / OAKBlank
37BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4163yes 2
38BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4161yes 2
39BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4162yes 2
40BT00012-1BEALLARA BEDSIDE / BLACK / WOOD EFFECT / CUT OUT HANDLE / 2 DRAWER / H 500 W 600 D 4160yes 2
Data
Cell Formulas
RangeFormula
D2:D40D2=IF(C2="","Blank",IF(COUNTIFS(A:A,A2,C:C,2),"yes 2","No 2"))
 
Upvote 0
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!
 
Last edited by a moderator:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top