COMMON NUMBERS

[sood]

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.

 

[Shane Devenshire]

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.

 

[sood]

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.