View Single Post
  #2   Report Post  
Jay07 Jay07 is offline
Member
 
Location: Birmingham
Posts: 35
Default

Quote:
Originally Posted by Dougie13 View Post
I have data in 20 Columns with about 2000 rows.

I have applied an Auto Filter to the columns.

The spreadsheet has multiple data entries in the rows and i want to somehow delete those multiple entries, leaving just one.

Eg: Row 545 has identical data in to line 546 and 547. I'd like to delete two of those rows to leave one.

Is there a simple way of doing this than picking the rows to delete out all individually say through a filter?


By the way. I'm running both Excel 2010 (Windows) and Excel 2011 (Mac).

Thanks
For Example...

----A----|----B----
1 Red
2 Blue
3 Green
4 Red
5 Purple
6 Black
7 Green

I would start by removing all filters, sort the column A-Z so it's like this...

----A----|----B----
1 Black
2 Blue
3 Green
4 Green
5 Purple
6 Red
7 Red

Then in Cell B2 enter this =A2=A1 and autofill through to the bottom.

That column will then read true/false. False if it is a unique record, true if it's a duplicate.

Filter to True, and delete all rows as normal.


There may well be a better way but that will do the job.