Removing duplicates

**bouncey** · 02-10-2010, 05:33 PM

I have a work book that gets updated daily, the only problem is it has duplicates going in and I have information put against certain items.

Is it possible to remove the duplicates based on C,E,H being identical? Column A is the date entered onto spreadsheet where I would need to leave the lastest info. Taking all that into account, if there are any comments in column I or J then those rows are to remain.

Sorry if I haven't made my self clear, I would post a mock speadsheet but don't know how.

Thanks in advance

**Richard Buttrey** · 02-10-2010, 07:08 PM

Hi,

The usual approach is to use a helper column and concatenate cells in columns C,E&H. e.g. in say K2 enter =C2&E2&H2 and copy down
Then in an other helper column say L2 enter =COUNTIF(K2:K$nn,K2) where nn is the last row number and copy down.

Now filter all your data using column K specifying a value >1. This will filter all the duplicate rows and you can then select and delete them.

if you still have problems upload a workbook by clicking on the 'Manage Attachments' below the message text box.

Regards

**broro183** · 02-10-2010, 07:28 PM

hi

Originally Posted by Richard Buttrey

...if you still have problems upload a workbook by clicking on the 'Manage Attachments' below the message text box.

To clarify Richard's comment, the "manage attachments" icon shows below the "Go Advanced" message box (but won't show if you use "Quick Reply").

Rob

**bouncey** · 02-10-2010, 07:31 PM

Thanks for the response Richard.

The above does work but I was hoping to use this in a macro I already have running for this project,but I am still a very inexperienced individual when it comes to writing code. Would you mind at all helping?

Again thanks for you help.

**Richard Buttrey** · 02-10-2010, 07:40 PM

Hi,

Yes, just upload the workbook and I'll take a look tomorrow.

Regards

**bouncey** · 02-10-2010, 07:54 PM

The first sheet represents workbook 1 which would be todays enquiry
The second sheet represents workbook 2 which would have all prior enquires
The third sheet is what I need to end up with after copying the contents of workbook 1 into workboook 2 and removing duplicates based on afore mentiond criteria.

Many thanks for your perseverance.

**Richard Buttrey** · 02-11-2010, 05:28 AM

Hi,

I don't understand the logic which results in the end product tab. For instance why does order no acs01 from wb1 not appear whereas acs09 appears twice albeit with a different Expd date.

Can you list the logic steps which you take to arrive at the end product mentioning the column data which is relevant to the decision process.

Regards

**bouncey** · 02-11-2010, 03:25 PM

New wb attached.
Rhe attached shows that although there where 9 new entries today, only one was a true entry, not a duplicate.

The first sheet shows todays enquiries. I want to either cut and paste into the second sheet and then remove duplicates leaving just one unique line, eg if C,E,H are duplicated in two or more rows then I would like to delete the newest entry, but only if there are no buyers comments (column J) if there are buyers comments then all other rows that are duplicated can be deleted.
or
do the removal of duplicates direct from sheet to sheet (no cut and paste).

I feel like I'm losing myself as I type this.

Thanks for your time.