Be Excellent To Each Other

I've got a big long list of alpha-num IDs.
I need to get rid of any IDs that are duplicated, and I don't mean just get rid of the extras, but the originals too.

So if my list was

I need to end up with


Can you beat my Google search?

PivotTable!

Create a PivotTable that counts the number of times each item occurs. If it is 1 then you can delete it off the list.

PivotTables are fun. Everyone should learn to use PivotTables.

If it is not 1, surely?
Also: What? Bear in mind that I don't use Excel. Ever.

Pivot Table is the way to go.

Though I get confused easily by Pivot Tables and would write a ridiculously complicated and inefficient macro to do it instead.

use the pivot table or do a countif in the next column along

ie make B2 =COUNTIF(A:A,A2)
make B3 =COUNTIF(A:A,A3)

and so on

and then filter or the c column to only display "1"

Malc

Yes, countif is the way I did it (I didn't use a pivot table, though).

Oh yeah, misread it a little.

*Select the column of ids (make sure it has a header at the top)
*From the menus select data>Pivottable and pivotchart report
*Go next, next, finish
*The next bit is a bit hard to explain. There should be a floating box in front of you with the column header in and on the main sheet itself a white box split into 4. Drag the column header into the two biggest boxes.
*It should now be a nice table with a count which you can copy and past and sort how you want.

Assuming your list is in column A, and starts in A2 (because there's a heading in A1), then type the following in B2 (or your next free column)

=countif(A:A, A2)

Drag that formula all the way down that column (highlight cell B2 and then double-click on the little square in the bottom right of the cell).

That should give you a count of how many times the corresponding cell appears in the list, so filter column B for everything greater than 1 (data > Filter > autofilter, click the dropdown on column B and choose custom, then greater than 1).

Then highlight all the rows you can still see and delete them.

Edit - although I've been beaten, my way gets rid of the duplicates, rather than just filtering for everything else. A pivot table would work for that as well, it just kind of depends whether you want to change your original data, or copy/paste elsewhere.

Edit again - Oh, I see you've done it now. I'll just shut up then.

I was implying copying the cells you could see to elsewhere. I assumed Grim... was clever enough to work that bit out for himself.

Malc

You've all been very helpful, but I have done it now, yes. I copied the whole list into anther sheet and did
Then I deleted all the 'yes' ones.