Suppose you have data like this.

See, there’s many repeated value in this data. You want to remove repeated value and only want to record unique value. Oh, you say "that’s easy. Remove one by one". Ok, In my example, I just give you 10 data. But what about 1000 data?. 10000 data?. You gonna remove one by one?. Don’t do that’s. That’s waste your time. Follow my way to cope this situation.

1. Copy the data into excel (You can use any excel version, I use Excel 2007)

2. After that, select all data, and sort the data in A-Z order (or reverse)

In this form, actually you can delete the same data manually. But one again, I won’t do that. All I need to do is the easier way to delete. Keep follow my trick.

3. Put a formula to check if data is the same. If so, report is as SAME. If not same, do not write anything

This can be done by writing a formula like this

I starting 2nd data and put checking formula to compare both cell value. If it’s the same value, put "SAME" in this cell, otherwise, don’t write anything.

Note: if your data consist of different case, use LOWER() or UPPER() function to compare

After that, you can copy this formula to cells below

4. Now you know where’s the same data, and where the unique data. But how to delete data with the same sign?

Just select all the data, and sort using SAME column. Here’s how to implement this

click that command bar, and then set Sort by : column C ( SAME sign colum). Here’s the result

See, easier way to remove duplicate lists. You can inspect that list repeat from ACA. Now, remove list from ACA and you got unique lists.

But why the SAME sign gone?. Yea of course, because SAME sign recalculate reordering. If you want to keep this sign,copy list in step number 4 (where sign SAME still exist, and then paste special/value . Here’s how

select all data/copy and then choose paste special in another correspond column

Repeat the sorting once again, and this is the result

Look, it’s easier to see the same value, because SAME sign lies there. Delete the correspond SAME row, and you’ll got unique data in your list

If you like this posts, give comments and share to your friends

Popularity: 12% [?]

About Admin

Blogging and, especially Wordpress lover. He can tweak small wordpress code to meet any need. Not a designer, though. Interested in programming world. He loves PHP and Visual Basic. You can argue, but I think, wordpress platform is the best blog platform today. Website | Email