Forum Discussion

TIGER-18's avatar
TIGER-18
Copper Contributor
Nov 13, 2023

highlight duplicates in sheets with 2 criteria

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:

 

sheet1sheet2sheet3

excel

11 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 13, 2023

    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?

     

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

    • TIGER-18's avatar
      TIGER-18
      Copper Contributor
      Nov 14, 2023

       

      Lorenzo 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 

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Nov 14, 2023

        Hi TIGER-18 

         

        In attached file a Power Query based solution:

        • Data in Sheet1-Sheet3 fhttps://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370
        • 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...

Resources