I have lists of various telephone numbers in multiple coulmns containing
different rows and there may be same numbers existing in these coulmns and
rows. I want to know which are those same numbers existing in these coulmns.
The said data is in a sheet from coulmn A to AR and every coulmn has
different rows upto2500. Can anyone help me to identify those common numbers
by using some formula so that I can make a list of common numbers existing in
column A to AR. I am using excel2003 and not well versed.Please guide in a
simple way. Thank you in anticipation.

Hi,

do the common numbers need to be on the same row in different columns or in
the same columns, or can they be anywhere - A1 contains 10 and Z2500 also
contains 10? I'm not clear if you mean by common numbers ones that appear
more than once, if so:

I would try this if the last is your situation:

1. In a column enter the following formula, lets say in AZ1:
=SMALL(A$1:R$2500,ROW(A1))
2. Copy this formula down until it returns #NUM! errors.
3. Covert the column to values
4. Use Data, Filter, Advanced filter, Unique records only, on this range to
produce a list of unique numbers. Lets suppose you put this list in
BA1:BA4000
5. In BB1 enter the formula
=COUNTIF($BA$1:$BA$4000,BA1)
copy this formula down
All formulas that return numbers 1 are repeats.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"sood" wrote:

I have lists of various telephone numbers in multiple coulmns containing
different rows and there may be same numbers existing in these coulmns and
rows. I want to know which are those same numbers existing in these coulmns.
The said data is in a sheet from coulmn A to AR and every coulmn has
different rows upto2500. Can anyone help me to identify those common numbers
by using some formula so that I can make a list of common numbers existing in
column A to AR. I am using excel2003 and not well versed.Please guide in a
simple way. Thank you in anticipation.

HI
Thank you for the response. Common i mean the number repeated more than once
in coulmns. I put the formula as you advised but I am not able to convert the
coulmn to values. Please explain in detail as i am not well versed with
excel. Thank you.

"Shane Devenshire" wrote:

Hi,

do the common numbers need to be on the same row in different columns or in
the same columns, or can they be anywhere - A1 contains 10 and Z2500 also
contains 10? I'm not clear if you mean by common numbers ones that appear
more than once, if so:

I would try this if the last is your situation:

1. In a column enter the following formula, lets say in AZ1:
=SMALL(A$1:R$2500,ROW(A1))
2. Copy this formula down until it returns #NUM! errors.
3. Covert the column to values
4. Use Data, Filter, Advanced filter, Unique records only, on this range to
produce a list of unique numbers. Lets suppose you put this list in
BA1:BA4000
5. In BB1 enter the formula
=COUNTIF($BA$1:$BA$4000,BA1)
copy this formula down
All formulas that return numbers 1 are repeats.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"sood" wrote:

I have lists of various telephone numbers in multiple coulmns containing
different rows and there may be same numbers existing in these coulmns and
rows. I want to know which are those same numbers existing in these coulmns.
The said data is in a sheet from coulmn A to AR and every coulmn has
different rows upto2500. Can anyone help me to identify those common numbers
by using some formula so that I can make a list of common numbers existing in
column A to AR. I am using excel2003 and not well versed.Please guide in a
simple way. Thank you in anticipation.