How to remove duplicate events in column
 

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

How to remove duplicate events in column
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scott" wrote in message
...
Hi,
I need to remove duplicate events from say: A1 to A500.
These are numbers only.
Any help will be greatly appreciated!

How to remove duplicate events in column
 

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?

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scott" wrote in message
...
Hi,
I need to remove duplicate events from say: A1 to A500.
These are numbers only.
Any help will be greatly appreciated!

How to remove duplicate events in column
 

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

"Scott" wrote:

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

How to remove duplicate events in column
 

"Scott" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to remove duplicate events in column
 

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
"Max" wrote:

"Scott" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to remove duplicate events in column
 

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

"Bob Umlas, Excel MVP" wrote:

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

"Scott" wrote:

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

How to remove duplicate events in column
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scott" wrote in message
...
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