Remove duplicates and report unique IDs

**meherenow9** · 11-09-2010, 02:53 PM

I have a list of IDs:

12
153
87
65
15
12
354
153
65
78
354
12

And I want the output to read:

12, 15, 65, 78, 87, 153, 354

It basically removes the duplicates and presents all the different IDs in an easy-to-read string sorted in numerical order from least to greatest.

Thanks in advance for your help!

**6StringJazzer** · 11-09-2010, 03:15 PM

For the first part, please see Help pages on

Sort
Advanced Filter (note option for Unique Values)

(You may want to add your Excel version to your profile so we can figure out how to give you more details on things like menu sequences to reach these.)

Once the list is sorted and unique, a quick and dirty way of converting to a list, assuming your list is in column A starting in A1, is to put this formula in B1:

=A1

and this formula in B2:

=A1&", "&A2

and then copy it down to the last row of your data. The last line will contain the complete list.

**meherenow9** · 11-09-2010, 04:47 PM

I was already aware of the sorting options. I was wondering if there was a way to automate the task. The "list of IDs" I will be inputting will change very frequently. I was hoping to simply cut-and-paste the list and see the output immediately without having to sort. If I have to sort every time, it will defeat the purpose of trying to find a quicker way to do it than I have now.

I imagine the required formula would be an array and would be rather complex in order to sort the IDs in numerical order.

Anyway, thanks for your help.

**antedenise** · 11-09-2010, 06:01 PM

1. Create a range called "Task_Name" for the column that will hold the id values.
2. then select another range of blank cells the same size as the original colum with ids
3. While the range is "selected" put the following formula in on the formula line and enter it using CTRL/Shift/Enter.
4 =INDEX(Task_Name,SMALL(IF(MATCH(Task_Name,Task_Name,0)=ROW(INDIRECT("1:"&ROWS(Task_Name))),MATCH(Task_Name,Task_Name,0),""),ROW(INDIRECT("1:"&ROWS(Task_Name)))))
5.This will return all of the unique values. There is a way to avoid the errors that come up when cells are not used in the output array, but I don't have a version of that available right now.

**meherenow9** · 11-09-2010, 06:21 PM

I appreciate all of your help. After some experimenting, I found that a simple COUNTIF() function for each possible ID takes care of the task:

=IF(COUNTIF(B2:B101,1)>0,"1 ","")&IF(COUNTIF(B2:B101,2)>0,"2 ","")&IF(COUNTIF(B2:B101,3)>0,"3 ","")&IF(COUNTIF(B2:B101,4)>0,"4","")

I replaced the 1, 2, 3, 4 with the appropriate range of IDs, resulting in a rather long formula, but one that worked perfectly.

Thanks for your suggestions!

**6StringJazzer** · 11-09-2010, 08:54 PM

Originally Posted by antedenise

1. Create a range called "Task_Name" for the column that will hold the id values.

I tried to replicate this and just to clarify, the named range must be limited to the specific cells holding the ID values, not the entire column. Using the entire column gave me #N/A.

It does a lovely job of listing unique values and it will take me a while to figure out how that formula works

Also note that there is a known bug in the board that sometimes inserts random spaces into long formulas, and you've got one there. The recommended alternative is to put it in a code box:

=INDEX(Task_Name,SMALL(IF(MATCH(Task_Name,Task_Name,0)=ROW(INDIRECT("1:"&ROWS(Task_Name))),MATCH(Task_Name,Task_Name,0),""),ROW(INDIRECT("1:"&ROWS(Task_Name)))))

meherenow9, it might have allowed us to find a good shortcut if you had mentioned initially that there is a specific set of allowable IDs. My procedural solution and antedenise's formula are for the general case and will handle any data.