Combine multiple excel sheet into one sheet, remove duplicates from the combined sheet

[anu_gv]

Hi,
I have multiple sheets in given workbook and I would like to combine them in one excel sheet. After I combine in one sheet, from the combined sheet I would like to remove the duplicate.
I was able to find macros to combined all in one sheet but i was not able to find macro for removing the duplicates.
Can any one help me out on this. I have searched on previously posted questions, I don't find it.

Here is the Macro for combining multiple sheet in one:

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub
=================================================================

Thanks in Advance.

Regards
Anu.

 

[Roderick_E]

What constitutes a duplicate? Is there a unique identifier in one of the columns?

 

[anu_gv]

Hi Roderick,

I have four columns used that will have same names

Thx & Regards
Anu.

 

[Roderick_E]

Hi Anu,
Hmm we need something unique to determine what is duplicated. A pro number? order number? something unique to that row. We could concatenate the entire row and consider that unique but requires more work.

 

[anu_gv]

Hi Roderick,
Name of the person who order, that will be on "Column E"

Thx & Regards
Anu

 

[anu_gv]

Hi All,

Is anyone have any solution to remove the duplicates from the combined sheet.

Really appreciate the response from experts.

Thx
Anu.

 

[Roderick_E]

Hi Roderick,
Name of the person who order, that will be on "Column E"

Thx & Regards
Anu

How about adapting this:

sheets("Combined").Columns("A:E").Select
    sheets("Combined").Range("$A$1:$E$10000").RemoveDuplicates Columns:=5, Header:=xlNo

You'd need to change Header:=xlNo to xlYes if you have headers.

 

[anu_gv]

Hi Roderick,

I can adopt that, but I don't want to change the header name that will defeat the whole purpose of combining all sheets in one.
Is there any other solution.
Thx
Anu.

 

[anu_gv]

Hello All,

Is there any other solution available to remove the duplicates from combined sheet.

Thx
Anu.

 

[anu_gv]

To All,

It worked using the below Macro.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Sheets("Combined").Columns("A:E").Select
Sheets("Combined").Range("$A$1:$E$10000").RemoveDuplicates Columns:=5, Header:=xlNo
Next
End Sub
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer


' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False


' Get count of records to search through.
iListCount = Sheets("Combined").Range("A1:A2000").Rows.Count
Sheets("Combined").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets("Combined").Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Combined").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Combined").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub