Duplicate numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kbkst - 16 Feb 2006 20:26 GMT

I am doing a copy and paste with phone numbers on my spreadsheet. I then
manually have to search for duplicates. I there a way that excel can do that
for me?

Also, When I copy phone numbers, I must copy one number, go to spreadsheet
and paste. Back & forth. Is there a way to highlight and copy all of the
numbers I need on the page in one shot and then paste to my spreadsheet?
Can't scroll down because they are far apart from eachother.

Hope someone can help.
kbkst

Reply to this Message

Mark - 16 Feb 2006 20:56 GMT

try using the autofilter option on your list of numbers and select the
option to display only unique items. Then you can copy that list.

Reply to this Message

Dave Peterson - 16 Feb 2006 22:03 GMT

What version of excel are you using?

> try using the autofilter option on your list of numbers and select the
> option to display only unique items. Then you can copy that list.

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 16 Feb 2006 22:04 GMT

Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm

> I am doing a copy and paste with phone numbers on my spreadsheet. I then
> manually have to search for duplicates. I there a way that excel can do that
[quoted text clipped - 11 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200602/1

Signature

Dave Peterson

Reply to this Message

George Gee - 16 Feb 2006 22:33 GMT

Assuming all your phone numbers are in column A:
In a blank column:
=IF(MAX(COUNTIF(A:A,A:A))>1,"Duplicate","Non Duplicate")
Copy this formula down the column.
If you need more help with this, post back.

George Gee

>I am doing a copy and paste with phone numbers on my spreadsheet. I then
> manually have to search for duplicates. I there a way that excel can do
[quoted text clipped - 9 lines]
> Hope someone can help.
> kbkst

Reply to this Message

kbkst - 22 Feb 2006 15:04 GMT

George Gee:
I need help. Cannot get this to work. My numbers are pasted in Column C
starting with row 2 and going down the page. I went into column D2 and typed
the formula changing the A's to C's. But how do I copy this formula into my
numbers in the previous column?
Thanks,
kbkst

>Assuming all your phone numbers are in column A:
>In a blank column:
[quoted text clipped - 9 lines]
>> Hope someone can help.
>> kbkst

Reply to this Message

George Gee - 22 Feb 2006 17:38 GMT

You don't copy the formula into the 'C' column.
You copy the formula into all the rows in the 'D' column,
alongside all the cells that contain your pasted numbers.
Copy and paste this formula into D2,
=IF(MAX(COUNTIF(C:C,C:C))>1,"Duplicate","Non Duplicate")
Drag it down the column as far as you have numbers in Column C.
Or with D2 selected, double-click the small black square at the
bottom right of the cell.

George Gee

> George Gee:
> I need help. Cannot get this to work. My numbers are pasted in Column C
[quoted text clipped - 19 lines]
>>> Hope someone can help.
>>> kbkst

Reply to this Message

kbkst - 22 Feb 2006 18:35 GMT

It worked!! Thank you so much. Do you know how to delete the duplicate
numbers? When I do a delete row for a number it deletes the first number and
then deletes all the text that says "duplicate", but does not delete the
numbers.

kbkst

>You don't copy the formula into the 'C' column.
>You copy the formula into all the rows in the 'D' column,
[quoted text clipped - 12 lines]
>>>> Hope someone can help.
>>>> kbkst

Reply to this Message

George Gee - 22 Feb 2006 18:57 GMT

I'm not really sure what you are trying to do!
Do you wish to delete all the rows that have 'Duplicate' in Column 'D'?

If yes, then select D2 > Click Data on the top toolbar > Filter >
Autofilter.
This will put a small down arrow above all columns that contain data.
Click the down arrow in column 'D' and click on 'Duplicates'
This will show all the duplicate rows.
Select all the rows you wish to delete, by highlighting the row header
numbers,
right-click, select 'Delete row'.

Practise on a copy of your worksheet first.

To show all rows again: Data > Filter > 'Show all'.

George Gee

> It worked!! Thank you so much. Do you know how to delete the duplicate
> numbers? When I do a delete row for a number it deletes the first number
[quoted text clipped - 21 lines]
>>>>> Hope someone can help.
>>>>> kbkst

Reply to this Message