How to remove duplicate events in column

[Scott]

Hi,
I need to remove duplicate events from say: A1 to A500.
These are numbers only.
Any help will be greatly appreciated!

 

[Max]

One way ..

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW())),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Select B1:C1, fill down to C500

Col B will return the unique items within col A,
all neatly bunched at the top

 

[Scott]

Hi Max,
I put the formulas in the cells but all I got was an error message that
sayis: error found, to accept change click yes.
Do I have to click: Ctrl+Shift+Enter when I put the first cell in and then
add to the column?

 

[Bob Umlas, Excel MVP]

Use Data/Filter/Advanced Filter: First select the range, then in the dialog
box check the Unique Items checkbox, and also check Copy to another location
& specify where you want the "new list".
Bob Umlas
Excel MVP

 

[Max]

I put the formulas in the cells
but all I got was an error message that
says: error found, to accept change click yes.
Do I have to click: Ctrl+Shift+Enter
when I put the first cell in and then add to the column?

No, just normal ENTER will do, they're non-array formulas

I'm not sure why you got the error, but ..
here's a sample construct to illustrate:
http://cjoint.com/?dCsjIYbE2u
Remove duplicates in column.xls

 

[Scott]

Hi Max,
Thanks, this worked after I realized that when I copy and paste the formula
for B1, it pasted in cell B1 and B2 because of the line break.
Now, if you can help me figure out how to either delete the A and C columns
so I can leave just the results in column B or eport just the results of
column B to a new worksheet.
I need to save this info. so I can do a mail merge in Word for lables.

Thank you for you expert knowlege!
Scott

 

[Scott]

Thanks Bob,
This fixed my problem!
Thank you vary much.
It great to have the expert help and this site has just that!

Scott Cleary

 

[Max]

so I can leave just the results in column B ..

well, we could always do an in-place:
Copy > Paste special > Check "Values" > OK
on col B (to freeze the values), then delete cols A & C

But if it's for one-off purposes,
then perhaps the easier approach would be Bob's