duplicates

rech340

New Member
Joined
Aug 27, 2002
Messages
7
I found one way for Excel to show me duplicates, and that was by using
conditional formatting with a formula like this:

IF (COUNTIF(RANGENAME, A1)>1, TRUE, FALSE)

This does work but treats each column in the range independently from the
column next to it. I want it to work like this:

A B
Mary Apple
Sue Banana
Joe Pineapple
Mary Kiwi
Mary Apple

It should only mark the 1st and the last row as being duplicates.

Thanks for any help.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
On 2002-08-28 19:51, rech340 wrote:
I found one way for Excel to show me duplicates, and that was by using
conditional formatting with a formula like this:

IF (COUNTIF(RANGENAME, A1)>1, TRUE, FALSE)

This does work but treats each column in the range independently from the
column next to it. I want it to work like this:

A B
Mary Apple
Sue Banana
Joe Pineapple
Mary Kiwi
Mary Apple

It should only mark the 1st and the last row as being duplicates.

Thanks for any help.

In an adjacent column, concatenate the two columns with a formula like

=B2&"|"&C2

Then determine if info in this column is duplicated.


The "|" is included in case you
have information like 1 11 and 11 1

With simple concatenation (=B3&C3) the result is 111 but the information is different,

With suggested formula

1|11
11|1
 
Upvote 0

Forum statistics

Threads
1,218,692
Messages
6,143,944
Members
450,517
Latest member
Rovex

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