Match duplicate data in 2 columns in a worksheet

G

Guest

I am working in 1 worksheet and have data in 2 columns and I would like to
search for duplicates and have the matches highlight automatically.

Below is an example:

Tenant Debit Credit
ABC $500
ABC $500

Then the 2 duplicate entries would be highlighted in blue.

I am somewhat of a beginner at the function entries, so please explain the
steps in detail.
 
G

Guest

I reviewed the section regarding duplicates; however,it only explains finding
duplicates in a single column. I need to match duplicates in 2 columns. How
do I do this??
 
G

Guest

I reviewed the section regarding duplicates; however,it only explains finding
duplicates in a single column. I need to match duplicates in 2 columns. How
do I do this??
 
G

Guest

High light your range. Assuming your range from A2:B100

Conditional Formatting, Select Formula IS

=SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))>1

Select Format Font, Color Blue, then click OK
 
G

Guest

High light your range. Assuming your range from A2:B100

Conditional Formatting, Select Formula IS

=SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))>1

Select Format Font, Color Blue, then click OK
 
R

Ragdyer

Which 2 columns?

Your example shows 3 columns!

Do you want to highlight just the two $500 entries?

Go to the original link:

http://www.cpearson.com/excel/topic.htm

Scroll down and click on:
"Duplicate Entries, Highlighting"

Then, simply follow the directions.

Just because Chip used an example range name of "range1", *doesn't* mean
that the range *must* be a *single* column.

Select your 2 columns, revise the formula to insert your top left cell of
your 2 column range, and follow directions.
 
R

Ragdyer

Which 2 columns?

Your example shows 3 columns!

Do you want to highlight just the two $500 entries?

Go to the original link:

http://www.cpearson.com/excel/topic.htm

Scroll down and click on:
"Duplicate Entries, Highlighting"

Then, simply follow the directions.

Just because Chip used an example range name of "range1", *doesn't* mean
that the range *must* be a *single* column.

Select your 2 columns, revise the formula to insert your top left cell of
your 2 column range, and follow directions.
 
G

Guest

Nobody, I entered in the below formula and nothing happened. Let me be more
specific with what I need:

First, I copy and paste my document into EXCEL and sort in alpha order by
the tenant’s name. Then I have to go down column 1 that are the payments
received and cross-reference with column 2, which are the charges and have
the matched data highlighted, so I can eliminate it to get to the bottom
number.

EXAMPLE:

Tenant Name Column1 Column2
TBELL 50.00
TBELL 50.00

I would like the system to highlight the 2 - 50.00 entries. Would I need to
do this as a conditional format or some other function? I really appreciate
your help!
 
G

Guest

Nobody, I entered in the below formula and nothing happened. Let me be more
specific with what I need:

First, I copy and paste my document into EXCEL and sort in alpha order by
the tenant’s name. Then I have to go down column 1 that are the payments
received and cross-reference with column 2, which are the charges and have
the matched data highlighted, so I can eliminate it to get to the bottom
number.

EXAMPLE:

Tenant Name Column1 Column2
TBELL 50.00
TBELL 50.00

I would like the system to highlight the 2 - 50.00 entries. Would I need to
do this as a conditional format or some other function? I really appreciate
your help!
 
G

Guest

RagDyer,

While I can surely appreciate your help in resolving this matter, I would
rather you not treat me like a dumbass. As I said in my initial posting...I
am relatively new to this process and would need a step by step explanation
on how to accomplish my task.

I have reviewed the Chip's directions and unfortunately I can not figure out
how to compare 2 columns on a single sheet. Can you assist me with this-minus
the sarcasm, please?
 
R

RagDyeR

I thought my *second* post contained enough information to help you do what
you wished.

For some reason, you chose not to answer that post, even possibly stating
that you were still confused.
*That* was the reason for the 3rd post, *not* that I thought you were a
"dumb ass".

As you read through these groups, you should notice that "sugar" generates
more help then "vinegar".
In this case, I'll forget your "vinegar" post.

From Chip's web page, as I directed you in my second post::
"Duplicate Entries, Highlighting"

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Our first task is to highlight the cells in Range1 that are duplicates. We
use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1.
Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional
Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the following
formula in the
formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and
select the font or
color you want your cell formatted with. Finally, click OK. Duplicate
entries in Range1 will be
formatted as you selected. For example, if "Able" occurs twice in Range1,
both
occurrences of "Able" will appear highlighted.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

1] Highlite entire Range 1

In your case, it might be B1 to C100

2] Then, select the Conditional Formatting tool from the Format menu:

While your range is *still* selected, from the menu bar, click:
<Format> <Conditional Formatting>

3]Change the "Cell Value Is" option to "Formula Is"

Expand the "Cell Value Is" box by clicking the small down arrow on the right
side,
And click on "Formula Is"

4] enter the following formula in the formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
Where A5 is the first cell in Range1

In your case, the formula would be:
=IF(COUNTIF($B$1:$C$100,B1)>1,TRUE,FALSE)

This formula does work, but it can shortened however to:

=COUNTIF($B$1:$C$100,B1)>1

5] Then, click the Format button and select the font or color you want your
cell formatted with.

This should be self explanatory.

6] Finally, click OK

As you can see, Chip explained it all ... *AND* ... in my second post ... I
explained that Range1 could be larger then a single column.

It would be nice now, that if this works or not, you reply back with the
result.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


RagDyer,

While I can surely appreciate your help in resolving this matter, I would
rather you not treat me like a dumbass. As I said in my initial posting...I
am relatively new to this process and would need a step by step explanation
on how to accomplish my task.

I have reviewed the Chip's directions and unfortunately I can not figure out
how to compare 2 columns on a single sheet. Can you assist me with
this-minus
the sarcasm, please?
 
G

Guest

Thank you it worked!!! I appreciate your help!!

RagDyeR said:
I thought my *second* post contained enough information to help you do what
you wished.

For some reason, you chose not to answer that post, even possibly stating
that you were still confused.
*That* was the reason for the 3rd post, *not* that I thought you were a
"dumb ass".

As you read through these groups, you should notice that "sugar" generates
more help then "vinegar".
In this case, I'll forget your "vinegar" post.

From Chip's web page, as I directed you in my second post::
"Duplicate Entries, Highlighting"

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Our first task is to highlight the cells in Range1 that are duplicates. We
use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1.
Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional
Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the following
formula in the
formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and
select the font or
color you want your cell formatted with. Finally, click OK. Duplicate
entries in Range1 will be
formatted as you selected. For example, if "Able" occurs twice in Range1,
both
occurrences of "Able" will appear highlighted.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

1] Highlite entire Range 1

In your case, it might be B1 to C100

2] Then, select the Conditional Formatting tool from the Format menu:

While your range is *still* selected, from the menu bar, click:
<Format> <Conditional Formatting>

3]Change the "Cell Value Is" option to "Formula Is"

Expand the "Cell Value Is" box by clicking the small down arrow on the right
side,
And click on "Formula Is"

4] enter the following formula in the formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
Where A5 is the first cell in Range1

In your case, the formula would be:
=IF(COUNTIF($B$1:$C$100,B1)>1,TRUE,FALSE)

This formula does work, but it can shortened however to:

=COUNTIF($B$1:$C$100,B1)>1

5] Then, click the Format button and select the font or color you want your
cell formatted with.

This should be self explanatory.

6] Finally, click OK

As you can see, Chip explained it all ... *AND* ... in my second post ... I
explained that Range1 could be larger then a single column.

It would be nice now, that if this works or not, you reply back with the
result.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


RagDyer,

While I can surely appreciate your help in resolving this matter, I would
rather you not treat me like a dumbass. As I said in my initial posting...I
am relatively new to this process and would need a step by step explanation
on how to accomplish my task.

I have reviewed the Chip's directions and unfortunately I can not figure out
how to compare 2 columns on a single sheet. Can you assist me with
this-minus
the sarcasm, please?


RagDyer said:
It's been a whole day now!

Have you tried using Chip's directions as I've suggested?
--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
Click to expand...
Click to expand...
 
R

RagDyer

You're welcome, and appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Kris said:
Thank you it worked!!! I appreciate your help!!

RagDyeR said:
I thought my *second* post contained enough information to help you do
what
you wished.

For some reason, you chose not to answer that post, even possibly stating
that you were still confused.
*That* was the reason for the 3rd post, *not* that I thought you were a
"dumb ass".

As you read through these groups, you should notice that "sugar"
generates
more help then "vinegar".
In this case, I'll forget your "vinegar" post.

From Chip's web page, as I directed you in my second post::
"Duplicate Entries, Highlighting"

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Our first task is to highlight the cells in Range1 that are duplicates.
We
use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1.
Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional
Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the following
formula in the
formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and
select the font or
color you want your cell formatted with. Finally, click OK. Duplicate
entries in Range1 will be
formatted as you selected. For example, if "Able" occurs twice in Range1,
both
occurrences of "Able" will appear highlighted.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

1] Highlite entire Range 1

In your case, it might be B1 to C100

2] Then, select the Conditional Formatting tool from the Format menu:

While your range is *still* selected, from the menu bar, click:
<Format> <Conditional Formatting>

3]Change the "Cell Value Is" option to "Formula Is"

Expand the "Cell Value Is" box by clicking the small down arrow on the
right
side,
And click on "Formula Is"

4] enter the following formula in the formula text box:
=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
Where A5 is the first cell in Range1

In your case, the formula would be:
=IF(COUNTIF($B$1:$C$100,B1)>1,TRUE,FALSE)

This formula does work, but it can shortened however to:

=COUNTIF($B$1:$C$100,B1)>1

5] Then, click the Format button and select the font or color you want
your
cell formatted with.

This should be self explanatory.

6] Finally, click OK

As you can see, Chip explained it all ... *AND* ... in my second post ...
I
explained that Range1 could be larger then a single column.

It would be nice now, that if this works or not, you reply back with the
result.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


RagDyer,

While I can surely appreciate your help in resolving this matter, I would
rather you not treat me like a dumbass. As I said in my initial
posting...I
am relatively new to this process and would need a step by step
explanation
on how to accomplish my task.

I have reviewed the Chip's directions and unfortunately I can not figure
out
how to compare 2 columns on a single sheet. Can you assist me with
this-minus
the sarcasm, please?


RagDyer said:
It's been a whole day now!

Have you tried using Chip's directions as I've suggested?
--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit
!
-------------------------------------------------------------------------- -
Nobody, I entered in the below formula and nothing happened. Let me
be
more
specific with what I need:

First, I copy and paste my document into EXCEL and sort in alpha
order by
the tenant's name. Then I have to go down column 1 that are the
payments
received and cross-reference with column 2, which are the charges and have
the matched data highlighted, so I can eliminate it to get to the
bottom
number.

EXAMPLE:

Tenant Name Column1 Column2
TBELL 50.00
TBELL 50.00

I would like the system to highlight the 2 - 50.00 entries. Would I
need
to
do this as a conditional format or some other function? I really
appreciate
your help!
:

High light your range. Assuming your range from A2:B100

Conditional Formatting, Select Formula IS

=SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))>1

Select Format Font, Color Blue, then click OK

---------------
mama no teeth

:

I am working in 1 worksheet and have data in 2 columns and I would like
to
search for duplicates and have the matches highlight
automatically.

Below is an example:

Tenant Debit Credit
ABC $500
ABC $500

Then the 2 duplicate entries would be highlighted in blue.

I am somewhat of a beginner at the function entries, so please explain
the
steps in detail.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula to post data in another spreadsheet 5
Compare 2 columns in a worksheet. 2
Comparing data in two columns and highlighting the data 6
Remove duplication accross 2 columns 1
Match a value from a column in worksheet 2
Lookup Data in Several Columns 4
Compare data on 2 separate worksheets. 3
VLOOKUP, trying to match cells in 2 columns.... help! 5

Top