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