count number of duplicates between 2 columns
 

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

Thanks... but hoping for a single formula with no helper columns... thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

No problme.

Use
=COUNTIF($B$1:$B$6,B1)
assuming you have values in Col B.

"SteveC" wrote:

Thanks... but hoping for a single formula with no helper columns... thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

oh, and one more thing, don't count number of matching blank cells

"SteveC" wrote:

Thanks... but hoping for a single formula with no helper columns... thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

I use
=COUNTIF($B$1:B1,B1)

This give me 1 against the first occurrence, 2 against the second and so
on... I can then retain the rows with 1 and delete others.

"SteveC" wrote:

Thanks... but hoping for a single formula with no helper columns... thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

That would do it if I was only worried about Column B, but I want to compare
it against Column A. I just posted a new question with more details about
the problem. Thanks for taking the time to help.


"Sheeloo" wrote:

No problme.

Use
=COUNTIF($B$1:$B$6,B1)
assuming you have values in Col B.

"SteveC" wrote:

Thanks... but hoping for a single formula with no helper columns... thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

Hi Steve

Try
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A6,B1:B6,0))))

--
Regards
Roger Govier

"SteveC" wrote in message
...
oh, and one more thing, don't count number of matching blank cells

"SteveC" wrote:

Thanks... but hoping for a single formula with no helper columns...
thanks...


"Sheeloo" wrote:

Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1)
and copy down

"SteveC" wrote:

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in
column B

the formula in this case would return: 2

thanks

count number of duplicates between 2 columns
 

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,B1:B6,0))))

--
Biff
Microsoft Excel MVP


"SteveC" wrote in message
...
Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins


Count the number of times that data in column A matches the data in column
B

the formula in this case would return: 2

thanks