• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing two columns for matches question

dgoscinski

New Member
I have a spreadsheet at work. We make 3 part numbers. Customer demand changes on a daily basis sometimes (the values you see). I am trying to come up with a way that show any differences for the same part ON the same day.

Column A is part number
Column B is the date we received their demand (This may not be needed in any formula)
Column C through E are dates.

Looking at the attached data set, Part A matches on 3/12/20--This is good. But does not match on the next 2 dates.
Looking at the data set, Part B does not match on 3/12/20--This is bad. Matches on the 2nd date. (Good) Does not match again on the 3rd. (Bad)
Looking at the data set Part C matches on all 3 dates.

I am looking for a formula or conditional formatting that will highlight the discrepancies. However, the criteria are Part Number AND date.

partFile date
3/12/2020​
3/13/2020​
3/14/2020​
part a
3/11/2020​
100​
90​
100​
part a
3/12/2020​
100​
100​
90​
Part b
3/11/2020​
102​
102​
90​
part b
3/12/2020​
90​
102​
100​
part c
3/11/2020​
200​
299​
100​
part c
3/12/2020​
200​
299​
100​

Conditional formatting gets me close. It will highlight fine in Column C. But the same condition would NOT work in Column E. Column E has 3 different parts with values of 100. I only want Part C to be differentiated as it is the only one that meets the same criteria: 1) same part AND same value on the same date.

I am not concerned which stands out matches or non-matches. (i.e. I don't care which cells are formatted as red, for instance.)

Hopefully, I explained my issue well. Bottom line is that I don't want to have to do this by hand. Trying to come up with a way to automate it, whether formula or formatting.

I would LIKE to see something like this: (or vice versa)

partFile date
3/12/2020​
3/13/2020​
3/14/2020​
part a
3/11/2020​
100​
90
100
part a
3/12/2020​
100​
100
90
Part b
3/11/2020​
102
102​
90
part b
3/12/2020​
90
102​
100
part c
3/11/2020​
200​
299​
100​
part c
3/12/2020​
200​
299​
100​

Thanks for your help.
 

Attachments

bosco_yip

Excel Ninja
Maybe,

Select C2:E7 >> click "Conditional Formatting" >> choose "New rule ">> choose "Use a formula…." >> in the rule box enter :

=COUNTIFS($A$2:$A$7,$A2,C$2:C$7,C2)=1

>> Click "Format" and choose your cell color (yellow in this example) >> OK >> Finish

66304

Regards
Bosco
 
Last edited:
Top