highlight duplicates in sheets with 2 criteria

Copper Contributor

I Have a workbook with multiple sheets and I want to highlight the row if the value in column A "Shipment NO" & column E "SKU"  both have been duplicated in other sheets below is what I'm looking for.

 

Sheet1

Shipment NO.Phone NO.INVOMaterialSKUDescription
H/2020/11/135580150569785103849168828875218245YLTB Frsh BW UT 12X300X2g
H/2020/10/180580150570785103849568828875218250YL(O2) UT 48X200g
H/2020/10/181580150571785103849668829130218250Estb ChargeRoll UT 12X100X2g
H/2020/10/182580150572785103849768828894218250 YLTB Fresh BW UT 24X150X2g
H/2020/10/183580150573785103849868828875218250 YLTP  (O2) UT 48X200g
H/2020/11/140580150574785103849968828884218250 YLTB Frsh BW UT 12X300X2g
H/2020/11/190580150575785103850068828884219955 YLTB Frsh BW UT 12X300X2g
H/2020/11/190580150576785103850168828875218250 YL Catering TB 2 gm
H/2020/11/192580150577785103850268871880218250 YLTB Fresh  GT UT 24X100X2g
H/2020/11/193580150578785103850368829130218250UT 12X100X2g

 

Sheet2

Shipment NO.Phone NO.INVOMaterialSKUDescription
H/2020/11/135580150569785103849168828875218245YLTB Frsh BW UT 12X300X2g
H/2020/11/136580150570785103849568828875218246YL(O2) UT 48X200g
H/2020/11/136580150571785103849668829130218247Estb ChargeRoll UT 12X100X2g
H/2020/11/136580150572785103849768828894218248 YLTB Fresh BW UT 24X150X2g
H/2020/11/190580150573785103849868828875219955 YLTP  (O2) UT 48X200g
H/2020/11/100580150574785103849968828884218250 YLTB Frsh BW UT 12X300X2g
H/2020/11/101580150575785103850068828884218250 YLTB Frsh BW UT 12X300X2g
H/2020/11/102580150576785103850168828875218252 YL Catering TB 2 gm
H/2020/11/103580150577785103850268871880218253 YLTB Fresh  GT UT 24X100X2g
H/2020/11/104580150578785103850368829130218268 YLTB Fresh  GT UT 24X100X2g

 

 

Sheet3

 

Shipment NO.Phone NO.INVOMaterialSKUDescription
H/2022/12/135580150569785103849168828875218245YLTB Frsh BW UT 12X300X2g
H/2022/12/136580150570785103849568828875218246YL(O2) UT 48X200g
H/2022/12/137580150571785103849668829130218247Estb ChargeRoll UT 12X100X2g
H/2022/12/138580150572785103849768828894218248 YLTB Fresh BW UT 24X150X2g
H/2022/12/139580150573785103849868828875218249 YLTP  (O2) UT 48X200g
H/2020/11/100580150574785103849968828884218250 YLTB Frsh BW UT 12X300X2g
H/2022/12/141580150575785103850068828884218250 YLTB Frsh BW UT 12X300X2g
H/2022/12/142580150576785103850168828875218252 YL Catering TB 2 gm
H/2022/12/143580150577785103850268871880218253 YLTB Fresh  GT UT 24X100X2g
H/2022/12/144580150578785103850368829130218268 YLTB Fresh  GT UT 24X100X2g

 

 

here is the result of what I'm looking for:

 

sheet1sheet1sheet2sheet2sheet3sheet3

11 Replies

Hi @TIGER-18 

 

I want to highlight the row if the value in column A "Shipment NO" & column B "SKU" (SKU are in column E)

 

#1 I have the following row highlighted in Sheet2. Misunderstanding on my side or your mistake?

Sample.png

 

#2 Are you on Windows with Excel >/= 2016 or on Mac with Excel 365 up to date?

SKU are in column E?

@L z. Hi

My mistake the column A "Shipment NO" & column E "SKU" i want to check if both values have been duplicated in other sheets.

 

I'm using windows 365 Microsoft.

 

regarding for shipment H/2020/11/190 I forgot to highlighted in sheet2 here the revised photo 

 

IMG_0561.jpeg

Yes ,the column A "Shipment NO" & column E "SKU"

Hi @TIGER-18 

 

In attached file a Power Query based solution:

  • Data in Sheet1-Sheet3 formatted as Tables
  • 3 queries - respectively for Sheet1, Sheet2 and Sheet3 - load on SHEET_TO_HIDE (TRUE means a duplicate was found in any of the other 2 sheets/tables)
  • Conditional Formatting rules in Sheet1-Sheet3 refer to the 3 tables in SHEET_TO_HIDE
  • The 3 queries auto-refresh when the file opens. You can also ask a Refresh by going to Data (tab) > Refresh All. It's also possible to configure the queries to auto-refresh every n minutes if you need...

@TIGER-18 

if with sql,

create temp table aa00 as
select colIndex[1:7] from consolidateSheet where regexp('\d+',f04) or rowid=1;
cli_no_header;
cli_mark_duplicate_rows~aa00~f03,f07;
select * from aaGrp;

 

 

https://b23.tv/QewP0Ei

Thank you so much but I prefer if you have idea to do formula in helper column then i will do conditional formatting against that helper column.

@TIGER-18
I'll see what I can do.... Please confirm you're fine with using Tables (instead of Ranges)

@TIGER-18 

 

I assumed Tables are fine as they offer comprehensive + dynamic ranges (amongst other benefits). Attached file contains what you asked (helper columns per sheet) and requires Excel 365

@L z. 

 

hi
thanks for your idea but there is an issue when I update any number of column E (SKU) the formula will reflect the error (Name?) please find the below photos:

1.JPG

 

Table 2 & 3

2.JPG

 

I prefer if there's a way to make a helper column with true and false and according to that, I will make conditional formatting against that column.

 

please note the sheets are increasing so I want something easy to apply for any new sheets.

 

thanks in advance.

Hi @TIGER-18 

Can't reproduce the issue here. Please share your workbook

If you can't attach it here upload and share it with OneDrive, Google Drive or the like then post the sharing link here

 

Thanks