If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
COMMON NUMBERS
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. |
#2
|
|||
|
|||
COMMON NUMBERS
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. |
#3
|
|||
|
|||
COMMON NUMBERS
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. |
Thread Tools | |
Display Modes | |
|
|