Formula in cond format to compare a different column once col A has a duplicate

danbates77

New Member
Joined
Jan 10, 2017
Messages
36
Office Version
  1. 2016
Hi,

I would like a formula that I can use in conditional format so if I have a duplicate in column A it then checks column E to see if that's the same or not.

I've created an example below.

As you can see col A has two 1's. So when this happens I would like the formula to then check col E and because the two numbers are different I would like it to fill in red

1CoffeeXI1445
2CoffeeXX1683
3MilkAA23416
4CoffeeJP19219
1CoffeeXI1261

Any help would be appreciated.

Thanks

Dan
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, which cells would you want red here, if any, and why?

22 01 14.xlsm
AE
115
223
3316
4419
511
611
715
823
924
10316
11316
CF Sample
 
Upvote 0
put CF on E1 with the formula =(VLOOKUP(A1,A:E,5,FALSE)<>E1) and apply that to all of column E.
 
Upvote 0
Solution
Hi,

Thank you both for replying to help me and mikerickson, you have got it spot on thank you.

Peter just to answer your question, it would be E5,E7 & E9 that I would require to be highlighted in red. I need this just to keep my data list up to date just in case we reuse and old process order which could load the wrong program.

Thank you both again for your help.

Dan
 
Upvote 0
mikerickson, you have got it spot on thank you.

Peter just to answer your question, it would be E5,E7 & E9 that I would require to be highlighted in red.
Then I think either ..

- my data is impossible or
- mikerickson doesn't have it spot on or
- your checking of what should be highlighted in my sample is wrong or
- your answer contained a typo

.. as the suggested CF formula highlights E5, E6 and E9 in my sample data.
 
Upvote 0
Hi Peter,

Just to clarify, your example data is fine. I wouldn't need E6 to be highlighted because once E5 was highlighted then I would do what I have to at work and then I would delete row 1 completely as it will no longer be required as a 5.

I hope this clears it up for you.

Thank you again for your help

Dan
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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