VBA to Extract Unique Values/Rows Across Multiple Sheets into One Sheet

jluangrath88

jluangrath88

New Member
Joined
Apr 22, 2018
Messages
23
Hi Experts!

Here's my current situation. And I would very much appreciate the help :)

I have 4 different worksheets. Let's call them: Sheet1, Sheet2, Sheet3, and Sheet4. The first 3 contains data that is delivered daily. Sheet4 is meant to dump the raw and compiled data between the first three. These first three sheets have rows that will contain some sort of duplicate value amongst them. I'm trying to extract values and its rows that do not have duplicates amongst the other sheets into one sheet (Sheet4).

For example (unique values underlined):
Sheet1 Contains:
[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]101010
[/TD]
[TD="align: center"] XXXX
[/TD]
[/TR]
[TR]
[TD="align: center"] 202020
[/TD]
[TD="align: center"] AAAA
[/TD]
[/TR]
[TR]
[TD="align: center"]212121
[/TD]
[TD="align: center"] BBBB
[/TD]
[/TR]
[TR]
[TD="align: center"]414141[/TD]
[TD="align: center"]ABAB
[/TD]
[/TR]
[TR]
[TD="align: center"]616161[/TD]
[TD="align: center"]XOXO
[/TD]
[/TR]
[TR]
[TD="align: center"]606060
[/TD]
[TD="align: center"]JAJA
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 Contains:
[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]101010[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]414141[/TD]
[TD]ABAB[/TD]
[/TR]
[TR]
[TD]616161[/TD]
[TD]XOXO
[/TD]
[/TR]
[TR]
[TD]80808
[/TD]
[TD]LOLO[/TD]
[/TR]
[TR]
[TD]909090[/TD]
[TD]RERE
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet3:
[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]909090[/TD]
[TD]CICI[/TD]
[/TR]
[TR]
[TD]101010[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]212121[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD]676767
[/TD]
[TD]GGGG
[/TD]
[/TR]
</tbody>[/TABLE]


The unique values between those three sheets would then be:
[TABLE="width: 128"]
<colgroup><col span="2" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]202020[/TD]
[TD="class: xl65, width: 64"]AAAA[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<colgroup><col span="2" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]606060[/TD]
[TD="class: xl65, width: 64"]JAJA
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<colgroup><col span="2" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]80808[/TD]
[TD="class: xl65, width: 64"]LOLO[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<colgroup><col span="2" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]676767[/TD]
[TD="class: xl65, width: 64"]GGGG
[/TD]
[/TR]
</tbody>[/TABLE]

The unique values between those three sheets need to be extracted to Sheet4.

I have the VBA code to remove duplicates from one sheet to the next. But I've been having to run the code twice as to make sure I cover all three sheets. However the multiple macros is making my workbook slow. So I'm wondering if there's a faster way I can do this with one code for all sheets. And then have those unique values compiled/copied/pasted into one specific worksheet.
 
Sort by date Sort by votes
I'm glad you posted a copy of the workbook as we would never have gotten a solution without as you were not letting us know key pieces of information such as your reference to the code name of the sheets not what actual tab name the user sees and the ID was in Col. A and Col B. not only Col. A.

In any case this should do the job (it did for me):

Code: 
Option Explicit
Sub Macro1()

    Dim xlnCalcMethod As XlCalculation
    Dim wsMySheet As Worksheet
    Dim wsSource As Worksheet
    Dim wsChildSheet1 As Worksheet
    Dim wsChildSheet2 As Worksheet
    Dim wsOutput As Worksheet
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim lngPasteRow As Long
    
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.CodeName = "Sheet1" Then 'Code tab name for 'Late In Summary'
            Set wsSource = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet2" Then 'Code tab name for 'Hub Absence Report'
            Set wsChildSheet1 = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet6" Then 'Code tab name for 'Time Off Request'
            Set wsChildSheet2 = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet4" Then 'Code tab name for 'Report'
            Set wsOutput = wsMySheet
        End If
    Next wsMySheet
    
    With wsSource
        lngLastRow = .Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For lngMyRow = 2 To lngLastRow
            If Evaluate("COUNTIF('" & wsChildSheet1.Name & "'!A:A,'" & wsSource.Name & "'!B" & lngMyRow & ")+COUNTIF('" & wsChildSheet2.Name & "'!F:F,'" & wsSource.Name & "'!B" & lngMyRow & ")") >= 1 Then
                On Error Resume Next 'Ignore error if there's no data on the 'wsOutput' sheet
                    lngPasteRow = wsOutput.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    lngPasteRow = lngPasteRow + 1
                On Error GoTo 0
                .Rows(lngMyRow).EntireRow.Copy Destination:=wsOutput.Rows(lngPasteRow).EntireRow
            End If
        Next lngMyRow
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Trebor76 said:
I'm glad you posted a copy of the workbook as we would never have gotten a solution without as you were not letting us know key pieces of information such as your reference to the code name of the sheets not what actual tab name the user sees and the ID was in Col. A and Col B. not only Col. A.

In any case this should do the job (it did for me):

Code: 
Option Explicit
Sub Macro1()

    Dim xlnCalcMethod As XlCalculation
    Dim wsMySheet As Worksheet
    Dim wsSource As Worksheet
    Dim wsChildSheet1 As Worksheet
    Dim wsChildSheet2 As Worksheet
    Dim wsOutput As Worksheet
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim lngPasteRow As Long
    
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.CodeName = "Sheet1" Then 'Code tab name for 'Late In Summary'
            Set wsSource = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet2" Then 'Code tab name for 'Hub Absence Report'
            Set wsChildSheet1 = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet6" Then 'Code tab name for 'Time Off Request'
            Set wsChildSheet2 = wsMySheet
        ElseIf wsMySheet.CodeName = "Sheet4" Then 'Code tab name for 'Report'
            Set wsOutput = wsMySheet
        End If
    Next wsMySheet
    
    With wsSource
        lngLastRow = .Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For lngMyRow = 2 To lngLastRow
            If Evaluate("COUNTIF('" & wsChildSheet1.Name & "'!A:A,'" & wsSource.Name & "'!B" & lngMyRow & ")+COUNTIF('" & wsChildSheet2.Name & "'!F:F,'" & wsSource.Name & "'!B" & lngMyRow & ")") >= 1 Then
                On Error Resume Next 'Ignore error if there's no data on the 'wsOutput' sheet
                    lngPasteRow = wsOutput.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    lngPasteRow = lngPasteRow + 1
                On Error GoTo 0
                .Rows(lngMyRow).EntireRow.Copy Destination:=wsOutput.Rows(lngPasteRow).EntireRow
            End If
        Next lngMyRow
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert
Click to expand...

Did I ever tell you that you are amazing!!! I just changed the ">=" to just "<" to get the non duplicates over to the "Report" sheet. So much faster than my old codes lol. You rock!:)
 
Upvote 0
Trebor76 said:
NP. Glad we got it sorted in the end. Thanks for the like :)
Click to expand...

Last question I swear lol. How do I get the rows to start on Row 2 of the "Report" worksheet because it's showing up on row 1. I can simply do a VBA to insert row and headers but If I can somehow add that to the existing code that would save me from doing a ton more codes lol.
 
Last edited:
Upvote 0
Try this in the appropriate section of my last macro:

Rich (BB code): 
On Error Resume Next 'Ignore error if there's no data on the 'wsOutput' sheet
    lngPasteRow = wsOutput.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngPasteRow = 0 Then
        lngPasteRow = 2 'Default row number if there's no data on the 'wsOutput' sheet. Change to suit if necessary.
    Else
        lngPasteRow = lngPasteRow + 1
    End If
On Error GoTo 0

Note I would change change the flag to identify unique entries to this...

Rich (BB code): 
If Evaluate("COUNTIF('" & wsChildSheet1.Name & "'!A:A,'" & wsSource.Name & "'!B" & lngMyRow & ")+COUNTIF('" & wsChildSheet2.Name & "'!F:F,'" & wsSource.Name & "'!B" & lngMyRow & ")")  = 0 Then

...as I feel it makes more sense. Having < 1 as you have done will work too.

Thanks,

Robert
 
Last edited:
Upvote 0
jluangrath88 said:
Last question I swear lol. How do I get the rows to start on Row 2 of the "Report" worksheet because it's showing up on row 1. I can simply do a VBA to insert row and headers but If I can somehow add that to the existing code that would save me from doing a ton more codes lol.
Click to expand...

Never mind I figured it out lol.
 
Upvote 0
Trebor76 said:
Try this in the appropriate section of my last macro:

Code: 
On Error Resume Next 'Ignore error if there's no data on the 'wsOutput' sheet
    lngPasteRow = wsOutput.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngPasteRow = 0 Then
        lngPasteRow = 2 'Default row number if there's no data on the 'wsOutput' sheet. Change to suit if necessary.
    Else
        lngPasteRow = lngPasteRow + 1
    End If
On Error GoTo 0

Note I would change change the flag to identify unique entries to this...

Code: 
If Evaluate("COUNTIF('" & wsChildSheet1.Name & "'!A:A,'" & wsSource.Name & "'!B" & lngMyRow & ")+COUNTIF('" & wsChildSheet2.Name & "'!F:F,'" & wsSource.Name & "'!B" & lngMyRow & ")") [B] = 0[/B] Then

...as [I]I[/I] feel it makes more sense. Having < 1 as you have done will work too.

Thanks,

Robert[/QUOTE]


Thank you I will give this a try too. :)
Click to expand...
 
Upvote 0
Trebor76 said:
Try this in the appropriate section of my last macro:

Code: 
On Error Resume Next 'Ignore error if there's no data on the 'wsOutput' sheet
    lngPasteRow = wsOutput.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngPasteRow = 0 Then
        lngPasteRow = 2 'Default row number if there's no data on the 'wsOutput' sheet. Change to suit if necessary.
    Else
        lngPasteRow = lngPasteRow + 1
    End If
On Error GoTo 0

Note I would change change the flag to identify unique entries to this...

Code: 
If Evaluate("COUNTIF('" & wsChildSheet1.Name & "'!A:A,'" & wsSource.Name & "'!B" & lngMyRow & ")+COUNTIF('" & wsChildSheet2.Name & "'!F:F,'" & wsSource.Name & "'!B" & lngMyRow & ")") [B] = 0[/B] Then

...as [I]I[/I] feel it makes more sense. Having < 1 as you have done will work too.

Thanks,

Robert[/QUOTE]

Perfect!!! Worked like a charm. Thanks so much.
Click to expand...
 
Upvote 0
Hello Mam,

Has your problem been solved of subjected query, if yes please provide me the solution of the same as i am also seeking its solution since so long,

additionaly is there any formulae to get the desired result instead of using vba code??

Thanks
Deepak
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA code to extract unique data across multiple sheets and multiple columns
Replies
1
Views
3K
mereslav
M
W
  • Question Question
Copy sheet with same row values to another sheet
Replies
2
Views
901
MARK858
MARK858
H
  • Question Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
1K
DanteAmor
DanteAmor
E
  • Question Question
Only unique values to a combobox from multiple columns, vba
2 3
Replies
20
Views
3K
Khanzaki
K
L
  • Question Question
VBA - copy new rows from different sheet, if condition is met
Replies
4
Views
2K
lotrev
L

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top