Delete duplicate rows based on more than one column with excel macro

[Shwapx]

I'm trying to create an excel macro that can delete duplicate rows and their original ones based on more than one column.

In the table below you will find the example data and the output for which I'm looking. I need to delete all duplicate rows but only look at some columns like in the example the first 3 headers.

Input table

header1	header2	header3	header4
Test	50	20	1
Test	50	20	2
Test	30	20	3
1	10	20	4
2	20	30	5
Test23	5	5	6
Test23	5	5	7

Output table after executing the macro

header1	header2	header3	header4
Test	30	20	3
1	10	20	4
2	20	30	5

 

[Shwapx]

Forward a copy of your sheet using the XL2BB add in and I'll happily have a look at it.

Example-sheet.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

Here is a link to the example file.

 

[kevin9999]

Example-sheet.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

Here is a link to the example file.

The problem is that what you have in column B (Add or Delete) doesn't match what you say in row 13.

Example-sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3	Some info about the date
4	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11
5
6	20210801	Add	test	test	20000	Test23	Test44	Test55	Test66	5	10
7	20210801	Delete	test	test1	10000	Test23	Test44	Test55	Test66	10	20
8	20210802	Add	Test23	asd	5	Test11	Test11	Test11	Test11	2	2
9	20210801	Delete	Test23	asd	5	Test11	Test11	Test11	Test11	3	3
10	20210803	Delete	Test1	asd	asd	Test11	Test12	Test13	Test11	1	2
11
12
13	From this table after the macro run I should have basically only the last row since it dosen't have duplicate. The first 4 rows are duplicates and should be deleted.
14
Sheet1

I'm going to ignore what you've written in column B and base the solution on the yellow highlighted cells.

 

[kevin9999]

OK, with this code:

Option Explicit
Sub Shwapx_V2()
    Dim ws As Worksheet
    Dim LRow As Long, LCol As Long
    Set ws = Worksheets("Sheet1")
    LRow = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    Application.ScreenUpdating = False
  
    With ws.Range(ws.Cells(6, LCol), ws.Cells(LRow, LCol))
        .Value = ws.Evaluate(Replace("C6:C#&"" | ""&F6:F#&"" | ""&G6:G#&"" | ""&H6:H#&"" | ""&I6:I#", "#", LRow))
    End With
  
    Dim ArrIn, ArrOut, i As Long
    ArrIn = ws.Range(ws.Cells(5, LCol), ws.Cells(LRow + 1, LCol))
    ReDim ArrOut(1 To UBound(ArrIn, 1) + 1, 1 To 1)
  
    For i = 2 To UBound(ArrIn, 1) - 1
        If ArrIn(i, 1) = ArrIn(i + 1, 1) Or ArrIn(i, 1) = ArrIn(i - 1, 1) Then ArrOut(i, 1) = 1
    Next i
    ws.Cells(5, LCol).Resize(UBound(ArrOut, 1)).Value = ArrOut
  
    With ws.Range(Cells(5, 1), Cells(LRow, LCol))
        .AutoFilter LCol, 1
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

This

Shwapx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Before
2
3	Some info about the date
4	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11
5
6	20210801	Add	test	test	20000	Test23	Test44	Test55	Test66	5	10
7	20210801	Delete	test	test1	10000	Test23	Test44	Test55	Test66	10	20
8	20210802	Add	Test23	asd	5	Test11	Test11	Test11	Test11	2	2
9	20210801	Delete	Test23	asd	5	Test11	Test11	Test11	Test11	3	3
10	20210803	Delete	Test1	asd	asd	Test11	Test12	Test13	Test11	1	2
11
Sheet1

Becomes this

Shwapx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	After
2
3	Some info about the date
4	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11
5
6	20210803	Delete	Test1	asd	asd	Test11	Test12	Test13	Test11	1	2
7
Sheet1

Is that what you were looking for?

 

[Shwapx]

Thank you so much I will take a look later at it since I'm busy at the moment. But as far as I can see from screenshots looks amazing!

 

[kevin9999]

Thank you so much I will take a look later at it since I'm busy at the moment. But as far as I can see from screenshots looks amazing!

Happy to help, and thanks for the feedback

 

[Shwapx]

Thank you it's working as expected. It's amazing. Just one last question is there a way to add the name of the excel file in the VBA so maybe I can export it and run it outside of excel? Export, the macro I mean and just run it and macro automatically open excel and does the magic . Since I will need to adjust this to 400+ excel files I can try to include name of the file. And in my case, name of the file is the same as name of the sheet Example: name is "Dog" and sheet name will be named "Dog" as well. And at the end maybe I can try combining all macros in one big file and run it over those 400+ files.

 

[kevin9999]

Thank you it's working as expected. It's amazing. Just one last question is there a way to add the name of the excel file in the VBA so maybe I can export it and run it outside of excel? Export, the macro I mean and just run it and macro automatically open excel and does the magic . Since I will need to adjust this to 400+ excel files I can try to include name of the file. And in my case, name of the file is the same as name of the sheet Example: name is "Dog" and sheet name will be named "Dog" as well. And at the end maybe I can try combining all macros in one big file and run it over those 400+ files.

It should be relatively straightforward, especially if all those 400+ files are in the same folder (loop through each file in folder) however, what you're asking is considerably more code and not related to your original post - therefore you should start a new thread. When you do, explain exactly what each file looks like (I'm assuming they're the same layout), what you want to do (include the code from this thread) and provide the name of the folder etc. You can't supply too much information FYI looping through files in folders isn't my area of interest, but there's plenty of other volunteers on this forum who'd will be willing to help, I'm sure

 

[Shwapx]

@kevin9999 Thanks. I didn't express myself correctly sorry about that. I wanted to ask if there is an option to include in that macro the name of the excel file and the sheet in which macro to operate. So I can export the macro and run it out of excel.

Let's say I have a folder that has 3 excel files and that macro. When I run the macro to run it over the correct excel file and sheet. This will save the time of opening the excel and running the macro manually. Thanks in advance.

 

[Shwapx]

Thanks i have found what i need. Thank you again!

 

[kevin9999]

Thanks i have found what i need. Thank you again!

Glad to hear you got it sorted out