Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 30th 12, 04:07 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2012
Posts: 4
Default Filtering out duplicate entries?

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

  #2   Report Post  
Old July 30th 12, 04:24 PM
Member
 
First recorded activity by ExcelBanter: Jul 2012
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filtering duplicate information Rachael Excel Worksheet Functions 3 September 10th 08 10:56 PM
filtering for repetitive entries TimmyD Excel Discussion (Misc queries) 1 November 3rd 07 12:24 AM
advanced filtering based on entries ABOVE those I wish to keep Cale Excel Discussion (Misc queries) 0 August 1st 06 09:11 PM
Custom filtering has only two entries (and, or)? Ive got at least TooN Excel Programming 3 July 21st 06 01:18 PM
filtering duplicate enteries David Hepner Excel Discussion (Misc queries) 0 August 29th 05 09:46 PM


All times are GMT +1. The time now is 12:01 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017