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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,219
Messages
5,745,446
Members
423,952
Latest member
EduardoM

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
Top