Excel07-Remove both duplicates multiple colum

May 15, 2011 at 12:42:38
Specs: Windows
Hi!
I have a 2 spreadsheets with names/addresses/phone/email.
I have combined them into one, now with duplicates.
I need to remove both rows, if they are an exact match across 4 of the columns-
This will leave me with only the rows that are NOT exactly the same- the people with changed info.
I can remove duplicates- but it leaves one.
I can macro to remove duplicates (both) but based on only one column.
I need to remove both rows if they are identical in 4 specific columns.

Example;
Jones Charlie 123 St OurTown 123-5896 email@email
Jones Jackie 123 St OurTown 123-5896 heremail@email
Jones Charlie 123St OurTown 123-5896 email@email
Jones Cathy 455St OurTown 236-8966 anotheremail@email
Jones Cathy 455St OurTown 699-9321 anotheremail@email
Jones Sue 789St OurTown 697-7969 greatemail@email

Columns A C and E only for duplicates, and remove both-
would leave me with Cathy's info ( because changed phone)
and Sue's because no duplicate at all. but the others would be gone.

I could even get by with conditional formatting to color the duplicates and sort them to the bottom of the spreadsheet.
Any ideas???
Thanks!
Kim


See More: Excel07-Remove both duplicates multiple colum

Report •

#1
May 15, 2011 at 13:31:42
re: "I can macro to remove duplicates (both) but based on only one column."

Create a column with concatenated values from your 4 columns and then use your "based on one column" macro to remove the duplicates based on that column.

Feel free to slap your forehead and say Duh! ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 15, 2011 at 14:36:24
Yes this is a DUH moment!
but now that I have been looking at this for hours and hours, I cant even see the mistake in my macro, either!
It is only deleting one of the two rows, too. and sometimes not deleting at all. I think because it only compares to the one directly above?
Arg.
Here is what I have: (T is my concatenate)
Sub DelDups()
'Determine the number of items in column T
 lastRow = Cells(Rows.Count, 2).End(xlUp).Row
On Error GoTo Done
'Loop through rows, starting at the bottom
  For delRow = lastRow To 1 Step -1
'If duplicates are found, delete both rows
  If Cells(delRow, 1) = Cells(delRow - 1, 1) Then
    Cells(delRow, 1).EntireRow.Delete
    Cells(delRow - 1, 1).EntireRow.Delete
   End If
 Next
Done:
End Sub

What have I screwed up now???
Is there another easier way??
I have 1500 names in this, and they ARE all in Access database, but unmatched Query is also based on one column.... maybe I will try to make a concatenate in Access... There are currently no keys-I have inherited this mess :)
Any suggestions???
Kim


Report •

#3
May 15, 2011 at 15:57:12
If the data is not sorted, then finding duplicates by comparing each cell only to the cell above it will be coincidental at best. Sorting the data would put the duplicates together.

If sorting the data "permanently" will be an issue, you can do all of the following within your macro:

1 - Add a column of sequential numbers in Column U
2 - Sort all of the data on Column T
3 - Remove the duplicates using your existing code
4 - Sort the data on Column U to put it back in it's original order
5 - Delete Columns T & U

BTW, the following is not "true":

'Determine the number of items in column T
 lastRow = Cells(Rows.Count, 2).End(xlUp).Row

Cells(Rows.Count, 2) refers to Column B, not Column T.

BTW.2 Are you familiar with the use of F8 to single step through your code to watch what it is doing, line by line? Size your VBA window so that you can see your spreadsheet behind it and press F8 repeatedly.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 15, 2011 at 16:36:07
Thanks for the tip DerbyDad!
I did not know about F8!
And after your insight- I realized I may be over thinking this-
So I made the concatenate and then used conditional formatting to highlight duplicates in it and then sorted the duplicated to the top and deleted!
Why didnt I think of that 5 hours ago??
Thanks so much for all your help DerbyDad!
Kim

Report •

Ask Question