Data comparison

  • Thread starter Thread starter Leanne
  • Start date Start date
L

Leanne

Hi,

Can someone help please?

I am trying to compare large lists of data containing
payroll numbers. I want to know which of the numbers in
col B match those in A.

Thanks
L
 
In addition, due to the way the data was been created, one
list of numbers is in the format that looks like "100",
the second list appears as "000100" in the cells but
as " '000100 " in the formula bar.
 
Hi Leanne,

place the following formula in cell C1
=IF(COUNTIF(Range("A:A",B1)>0,"Duplicate","")

You later gave another part if column B has the leading
zeros making them text try this formula
=IF(COUNTIF(Range("A:A",B1*1)>0,"Duplicate","")

Use the fill handle to copy the formula down from row1
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at Chip Pearson's
Duplicate And Unique Items In Lists
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

For more information on COUNTIF
your Excel help, and also John Walkenbach's
Excel Developer Tip: Cell Counting Techniques
http://www.j-walk.com/ss/excel/tips/tip52.htm
 
Thanks David,

Maybe I'm having a blonde moment, or maybe it's just that
it's Monday morning....but those formulas are suggesting
that I've "entered too many arguements"
 
Hi Leanne, (corrected formula)
Sorry the formula should be
=IF(COUNTIF(A:A,B1),"Duplicate","")
and the multiplication attempt to force a text constant to
a number was unnecessary.
--
 
Back
Top