Conditional Formatting and Match Cells to remove data in below cells

D

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to show 1 value 'Top Row = Yes' if Column B is the Repeatable. For example Line 3 to 6. IF It's not repeatable then it will still be Top ROW. I will use Conditional Formatting to show me items which are not duplicates.

TEST FILE MARCH.xlsx
ABCDE
1Unique Internal IDProduct Family IDSIMPLE PRODUCT?SKUName
287318731SIMPLEKEMKEM001/PKOsto EZ Vent
343024298503407/BXCenterPointLock Stoma Cap
443014298503404/BXCenterPointLock Stoma Cap
543004298503403/BXCenterPointLock Stoma Cap
642994298503402/BXCenterPointLock Stoma Cap
753015299SIMPLE007-3186/BXStoma Cap
88964689642007-401922/BXSur-Fit Natura Dose Kit
9896438964251401924/BXSur-Fit Natura Dose Kit
108804988045007-7815/BXAdapt Barrier Rings
118804888045007-7806/BXAdapt Barrier Rings
128804788045007-100798/BXAdapt Barrier Rings
138804688045007-100798/EAAdapt Barrier Rings
148595285950SIMPLE007-102518/BXAdapt CeraRing Barrier Ring
158524885246007-101100/BXNew Image Skin Barrier
1685247852465019303/BXNew Image Skin Barrier
17847394048503731/BXCenterPointLock Skin Barrier
18847384048007-3730/BXCenterPointLock Skin Barrier
1984571845655089603/BXAdapt CeraRing Barrier Rings
2084570845655089602/BXAdapt CeraRing Barrier Rings
2184569845655089601/BXAdapt CeraRing Barrier Rings
2284568845655089540/BXAdapt CeraRing Barrier Rings
2384567845655089530/BXAdapt CeraRing Barrier Rings
2484566845655089520/BXAdapt CeraRing Barrier Rings
253936939369SIMPLERRSNS31004/BXSkin Barrier Ring
263871638714RRSNS684U4/BXConforming Skin Barrier Ring
273871538714RRSNS684U2/BXConforming Skin Barrier Ring
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BCell ValueduplicatestextNO


This is what I'm trying to show -

Unique Internal IDProduct Family IDSIMPLE PRODUCT?TOP ROWSKU
87318731SIMPLETRUEKEMKEM001/PK
43024298TRUE503407/BX
43014298503404/BX
43004298503403/BX
42994298503402/BX
53015299SIMPLETRUE007-3186/BX
8964689642TRUE007-401922/BX
896438964251401924/BX
8804988045TRUE007-7815/BX
8804888045007-7806/BX
8804788045007-100798/BX
8804688045007-100798/EA
8595285950SIMPLETRUE007-102518/BX
8524885246TRUE007-101100/BX
85247852465019303/BX
847394048TRUE503731/BX
847384048007-3730/BX
8457184565TRUE5089603/BX
84570845655089602/BX
84569845655089601/BX
84568845655089540/BX
84567845655089530/BX
84566845655089520/BX
3936939369SIMPLETRUERRSNS31004/BX
3871638714TRUERRSNS684U4/BX
3871538714RRSNS684U2/BX


IF the TOP ROW is True, then I want the TITLE to display empty for the remaining cells underneath, for example - (THIS WILL apply to other fields in the spreadsheet for example (Description, and Images. Because everything is a duplicate I can safely delete it as the TOP ROW has the information).

TOP ROWSKUName
TRUEKEMKEM001/PKOsto EZ Vent
TRUE503407/BXCenterPointLock Stoma Cap
503404/BX
503403/BX
503402/BX
TRUE007-3186/BXStoma Cap
TRUE007-401922/BXSur-Fit Natura Dose Kit
51401924/BX
TRUE007-7815/BXAdapt Barrier Rings
007-7806/BX
007-100798/BX
007-100798/EA
TRUE007-102518/BXAdapt CeraRing Barrier Ring
TRUE007-101100/BXNew Image Skin Barrier
5019303/BX
TRUE503731/BXCenterPointLock Skin Barrier
007-3730/BX
TRUE5089603/BXAdapt CeraRing Barrier Rings
5089602/BX
5089601/BX
5089540/BX
5089530/BX
5089520/BX
TRUERRSNS31004/BXSkin Barrier Ring
TRUERRSNS684U4/BXConforming Skin Barrier Ring
RRSNS684U2/BX


Novice Excel User :( please can anyone guide me. Sorry couldn't delete my other thread based on this (Reason for creating a new thread: Old thread maybe too complex to understand, and couldn't amend).

Thanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
How about
+Fluff 1.xlsm
ABCDEF
1Unique Internal IDProduct Family IDSIMPLE PRODUCT?SKUName
287318731SIMPLEKEMKEM001/PKOsto EZ VentTRUE
343024298503407/BXCenterPointLock Stoma CapTRUE
443014298503404/BXCenterPointLock Stoma Cap 
543004298503403/BXCenterPointLock Stoma Cap 
642994298503402/BXCenterPointLock Stoma Cap 
753015299SIMPLE007-3186/BXStoma CapTRUE
88964689642007-401922/BXSur-Fit Natura Dose KitTRUE
9896438964251401924/BXSur-Fit Natura Dose Kit 
108804988045007-7815/BXAdapt Barrier RingsTRUE
118804888045007-7806/BXAdapt Barrier Rings 
128804788045007-100798/BXAdapt Barrier Rings 
138804688045007-100798/EAAdapt Barrier Rings 
148595285950SIMPLE007-102518/BXAdapt CeraRing Barrier Ring TRUE
158524885246007-101100/BXNew Image Skin BarrierTRUE
1685247852465019303/BXNew Image Skin Barrier 
17847394048503731/BXCenterPointLock Skin BarrierTRUE
18847384048007-3730/BXCenterPointLock Skin Barrier 
1984571845655089603/BXAdapt CeraRing Barrier RingsTRUE
2084570845655089602/BXAdapt CeraRing Barrier Rings 
2184569845655089601/BXAdapt CeraRing Barrier Rings 
2284568845655089540/BXAdapt CeraRing Barrier Rings 
2384567845655089530/BXAdapt CeraRing Barrier Rings 
2484566845655089520/BXAdapt CeraRing Barrier Rings 
253936939369SIMPLERRSNS31004/BXSkin Barrier RingTRUE
263871638714RRSNS684U4/BXConforming Skin Barrier RingTRUE
273871538714RRSNS684U2/BXConforming Skin Barrier Ring 
Main
Cell Formulas
RangeFormula
F2:F27F2=IF(COUNTIFS(B$2:B2,B2)=1,TRUE,"")
 
Upvote 0
Thanks, it gives me what I want.

Any chance you could help me with the other bit? i.e. The Title (Name) is blank when it's True? I needs to go down a list which I believe is either a Index or Match. I'm still trying to play around with this. If I can get this working then i can copy the same formula for Images, and other columns all referring to this column.

Thank you
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFG
1Unique Internal IDProduct Family IDSIMPLE PRODUCT?SKUName
287318731SIMPLEKEMKEM001/PKOsto EZ VentTRUEOsto EZ Vent
343024298503407/BXCenterPointLock Stoma CapTRUECenterPointLock Stoma Cap
443014298503404/BXCenterPointLock Stoma Cap  
543004298503403/BXCenterPointLock Stoma Cap  
642994298503402/BXCenterPointLock Stoma Cap  
753015299SIMPLE007-3186/BXStoma CapTRUEStoma Cap
88964689642007-401922/BXSur-Fit Natura Dose KitTRUESur-Fit Natura Dose Kit
9896438964251401924/BXSur-Fit Natura Dose Kit  
108804988045007-7815/BXAdapt Barrier RingsTRUEAdapt Barrier Rings
118804888045007-7806/BXAdapt Barrier Rings  
128804788045007-100798/BXAdapt Barrier Rings  
138804688045007-100798/EAAdapt Barrier Rings  
148595285950SIMPLE007-102518/BXAdapt CeraRing Barrier Ring TRUEAdapt CeraRing Barrier Ring
158524885246007-101100/BXNew Image Skin BarrierTRUENew Image Skin Barrier
1685247852465019303/BXNew Image Skin Barrier  
17847394048503731/BXCenterPointLock Skin BarrierTRUECenterPointLock Skin Barrier
18847384048007-3730/BXCenterPointLock Skin Barrier  
1984571845655089603/BXAdapt CeraRing Barrier RingsTRUEAdapt CeraRing Barrier Rings
2084570845655089602/BXAdapt CeraRing Barrier Rings  
2184569845655089601/BXAdapt CeraRing Barrier Rings  
2284568845655089540/BXAdapt CeraRing Barrier Rings  
2384567845655089530/BXAdapt CeraRing Barrier Rings  
2484566845655089520/BXAdapt CeraRing Barrier Rings  
253936939369SIMPLERRSNS31004/BXSkin Barrier RingTRUESkin Barrier Ring
263871638714RRSNS684U4/BXConforming Skin Barrier RingTRUEConforming Skin Barrier Ring
273871538714RRSNS684U2/BXConforming Skin Barrier Ring  
28
Main
Cell Formulas
RangeFormula
F2:F27F2=IF(COUNTIFS(B$2:B2,B2)=1,TRUE,"")
G2:G27G2=IF(F2=TRUE,E2,"")


Also please do not quote entire posts, it just clutters up the post.
 
Upvote 0
Solution
THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU!

Can I buy you a coffee or something? I swear I've been struggling with this for almost 2-3 weeks. I felt so stressed, depressed and I told by boss I can't do this. It's to complicated as it's 8000 products and re-structuring the file data to fit the system would take months as again I'm not that good lol and have to do this manually. But you saved my life! I didn't think of coming on here before and well.... please sir! let me send you something your way.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi,
I'm stumbled onto a new problem. So on my sheets, to define the variants we have 6 columns, 3 of them are just Titles and 3 of them are actual values. So the issue is most of these are duplicates not all of them, see this example -

FINAL MASTER IMPORT FILE.xlsx
BCDO
1Unique Internal IDProduct Family IDTop Row
21842718425TRUESize 120" Application Security Pole Sold By Each
31842618425 Size 120" Application Security Pole Sold By Each
41659616594TRUESize 7' - 10' Application Pole with Curved Bar Sold By Each
51659516594 Size 7' - 10' Application Pole with Curved Bar Sold By Each
6465463TRUEDescription Overall Width: 3" - 7" x Overall Height: 14.5" - 17" Application Tub Clamp Rail Sold By Each
7464463 Description Overall Width: 3" - 7" x Overall Height: 14.5" Application Tub Clamp Rail Sold By Each
86997669974TRUEColor White Application Splash Shield Sold By Case of 12
96997569974 Color White Application Splash Shield Sold By Each
1084618459TRUEColor Rose Application Sitz Bath Sold By Case of 10
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=IF(COUNTIFS(C$2:C2,C2)=1,TRUE,"")
O2:O10O2=CONCAT(I2," ",J2," ",K2," ",L2," ",M2," ",N2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:CCell ValueduplicatestextNO


The first 4 are duplicates (I've just concated these 6 columns. The 4 I'm talking about here are 2 different Parent IDs).

I've tried to use the Match Formula but I can't search via the Parent ID. Like for example, IF Parent ID then search through all 2 Parent IDS if duplicates show me 'Duplicate' else 'Unique'. Again it needs to go through a list. I've tried to use your formulas like
Code: 
 =IF(D2=TRUE,COUNTIFS(O$2:O2,O2),"Duplicate","Unique")
but it's not working :(

Hope you can help.

Thanks
 
Upvote 0
It would help if you explain what you mean by "Parent ID" & "2 Parent IDS" it would also help if you could show where you have used the formula & explain how it's not working.
 
Upvote 0
Hi Fluff,
Sorry so what I mean is in Column C which has the Parent ID is the one im referring to. The Unique ID is the Variant ID so what I'm saying is, If I can get a formula to go through these Parent IDs but to search for Column O then I can see what values are duplicate here.

[
FINAL MASTER IMPORT FILE.xlsx
BCDOP
1Unique Internal IDProduct Family IDTop Row
21842718425TRUESize 120" Color White Sold By EachDuplicate
31842618425 Size 120" Color Black Sold By EachDuplicate
41659616594TRUESize 7' - 10' Application Pole with Curved Bar Sold By EachDuplicate
51659516594 Size 7' - 10' Application Pole with Curved Bar Sold By EachDuplicate
6465463TRUEDescription Overall Width: 3" - 7" x Overall Height: 14.5" - 17" Application Tub Clamp Rail Sold By EachDuplicate
7464463 Description Overall Width: 3" - 7" x Overall Height: 14.5" Application Tub Clamp Rail Sold By EachDuplicate
86997669974TRUEColor White Application Splash Shield Sold By Case of 12Duplicate
96997569974 Color White Application Splash Shield Sold By EachDuplicate
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=IF(COUNTIFS(C$2:C2,C2)=1,TRUE,"")
O2:O9O2=CONCAT(I2," ",J2," ",K2," ",L2," ",M2," ",N2)
P2:P9P2=IF(COUNTIFS(O$2:O2,O2),"Duplicate","Unique")


So as you can see the Parent IDs are the same here highlighted and what I want to do is for it to tell me which value is duplicate in just that range of Parent IDs.

If the Value in Column O is 'Size 120" Color White Sold By Each' in Parent ID range 0001 but then the same value is shown in column O 'Size 120" Color White Sold By Each' but in a different Parent ID range 0002 then I don't want it to show it's a duplicate. As each product is different then it doesn't matter but it cannot be same in the same range of IDs.

Hope that helps.
 
Upvote 0
I believe this is the formula I need to check both fields. I've tested it and it seems right but can you just double check just in case I'm missing something?

Code: 
=IF(COUNTIFS($C$2:$C2,$C2,$O$2:$O2,$O2)>1, "Duplicate row", "")
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,110
Messages
6,123,147
Members
449,098
Latest member
Doanvanhieu

Share this page

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