Data comparison

[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

 

[Leanne]

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.

 

[David McRitchie]

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

 

[Leanne]

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"

-----Original Message-----
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#TaggingDuplicate
s

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

.

 

[David McRitchie]

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.
--

 

[Leanne]

That's excellent David.... that'll save me hours!!

Thanks

-----Original Message-----
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"

-----Original Message-----
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#TaggingDuplicat

e

s

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

.

.