Formulas for telephone numbers: finding duplicates, autoformat
 

- How can I check (I need a formula) if a column contains same telephone
number twice or not? It is too slow to check manually (data / sort)

- How could I autoformat telephone numbers, like 040123456 = 040-123
456, so the numbers would be easier to read on screen? I will not copy
phone numbers to any other application, it is sufficient the numbers
will be readable in Excel, so no hard space bars are necessary.

Add a formula in B1

=IF(COUNTIF($A$1:A1,A1)1,"Dup","")

and copy down.

Custom format (FormatCellsCustom) of 000-000000

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sandeep Elbak" wrote in message
...
- How can I check (I need a formula) if a column contains same telephone
number twice or not? It is too slow to check manually (data / sort)

- How could I autoformat telephone numbers, like 040123456 = 040-123
456, so the numbers would be easier to read on screen? I will not copy
phone numbers to any other application, it is sufficient the numbers
will be readable in Excel, so no hard space bars are necessary.

For formatting, right click a cell, select "Format Cells".
On the "Number Tab" select "Custom" from the "Category" list
Enter 000-000-000 as the format (in the text box below to the right)

For finding duplicates, you could use a a simple CountIf
EG.
Enter a list of phone numbers in Col A
Enter the function =COUNTIF($A$1:$A$500, A1) in B1
Copy B1 down as far as your list is long
If any cell in Col B is 1 then it is a duplicate.




"Sandeep Elbak" wrote in message ...
- How can I check (I need a formula) if a column contains same telephone number twice or not? It is too slow to check
manually (data / sort)

- How could I autoformat telephone numbers, like 040123456 = 040-123 456, so the numbers would be easier to read on
screen? I will not copy phone numbers to any other application, it is sufficient the numbers will be readable in
Excel, so no hard space bars are necessary.

Hi Sandeep,

Here is little more Conditional Formatting to indicate duplicates
with identification of row location of the original phone number

Example in: http://www.mvps.org/dmcritchie/excel...htm#duplicates

Duplicate identifications
(yellowish first among duplicates, greenish for actual duplicates)
formula 1 is: =IF(COUNTIF($A:$A,$A1)1,COUNTIF($A$1:$A1,$A1)=1)
formula 2 is: =IF(COUNTIF($A:$A,$A1)1,COUNTIF($A$1:$A1,$A1)1)

Identification of the row number for the original value (somewhere on row 1)
=IF(COUNTIF($A$1:$A1,$A1)1,MATCH($A1,$A$1:$A1,0), "")

But the identification formula would start somewhere on the second row
if you had column headers on row 1.
=IF(COUNTIF($A$1:$A2,$A2)1,MATCH($A2,$A$1:$A2,0), "")
as if you started on row 1 with the original, because you want to
identify the actual row number of the original.

And use of the fill handle to copy formulas down
http://www.mvps.org/dmcritchie/excel/fillhand.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

"Sandeep Elbak" wrote in message ...
- How can I check (I need a formula) if a column contains same telephone
number twice or not? It is too slow to check manually (data / sort)

- How could I autoformat telephone numbers, like 040123456 = 040-123
456, so the numbers would be easier to read on screen? I will not copy
phone numbers to any other application, it is sufficient the numbers
will be readable in Excel, so no hard space bars are necessary.