software

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

One way ..

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

Put in C1:
=IF(A1=quot;quot;,quot;quot;,IF(COUNTIF($A$1:A1,A1)gt;1,quot;quot;,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
savefile.com/projects/236895
xdemechanik
---
quot;Scottquot; gt; wrote in message
...
gt; Hi,
gt; I need to remove duplicate events from say: A1 to A500.
gt; These are numbers only.
gt; Any help will be greatly appreciated!
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?

quot;Maxquot; wrote:

gt; One way ..
gt;
gt; Put in B1:
gt; =IF(ISERROR(SMALL(C:C,ROW())),quot;quot;,
gt; INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))
gt;
gt; Put in C1:
gt; =IF(A1=quot;quot;,quot;quot;,IF(COUNTIF($A$1:A1,A1)gt;1,quot;quot;,ROW()))
gt;
gt; Select B1:C1, fill down to C500
gt;
gt; Col B will return the unique items within col A,
gt; all neatly bunched at the top
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Scottquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt; I need to remove duplicate events from say: A1 to A500.
gt; gt; These are numbers only.
gt; gt; Any help will be greatly appreciated!
gt;
gt;
gt;

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
amp; specify where you want the quot;new listquot;.
Bob Umlas
Excel MVP

quot;Scottquot; wrote:

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

quot;Scottquot; wrote:
gt; I put the formulas in the cells
gt; but all I got was an error message that
gt; says: error found, to accept change click yes.
gt; Do I have to click: Ctrl Shift Enter
gt; 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:
cjoint.com/?dCsjIYbE2u
Remove duplicates in column.xls
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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
quot;Maxquot; wrote:

gt; quot;Scottquot; wrote:
gt; gt; I put the formulas in the cells
gt; gt; but all I got was an error message that
gt; gt; says: error found, to accept change click yes.
gt; gt; Do I have to click: Ctrl Shift Enter
gt; gt; when I put the first cell in and then add to the column?
gt;
gt; No, just normal ENTER will do, they're non-array formulas
gt;
gt; I'm not sure why you got the error, but ..
gt; here's a sample construct to illustrate:
gt; cjoint.com/?dCsjIYbE2u
gt; Remove duplicates in column.xls
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;
gt;
gt;

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

quot;Bob Umlas, Excel MVPquot; wrote:

gt; Use Data/Filter/Advanced Filter: First select the range, then in the dialog
gt; box check the Unique Items checkbox, and also check Copy to another location
gt; amp; specify where you want the quot;new listquot;.
gt; Bob Umlas
gt; Excel MVP
gt;
gt; quot;Scottquot; wrote:
gt;
gt; gt; Hi,
gt; gt; I need to remove duplicate events from say: A1 to A500.
gt; gt; These are numbers only.
gt; gt; Any help will be greatly appreciated!

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

well, we could always do an in-place:
Copy gt; Paste special gt; Check quot;Valuesquot; gt; OK
on col B (to freeze the values), then delete cols A amp; C

But if it's for one-off purposes,
then perhaps the easier approach would be Bob's
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Scottquot; gt; wrote in message
...
gt; Hi Max,
gt; Thanks, this worked after I realized that when I copy and paste the
formula
gt; for B1, it pasted in cell B1 and B2 because of the line break.
gt; Now, if you can help me figure out how to either delete the A and C
columns
gt; so I can leave just the results in column B or eport just the results of
gt; column B to a new worksheet.
gt; I need to save this info. so I can do a mail merge in Word for lables.
gt;
gt; Thank you for you expert knowlege!
gt; Scott