[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Excel - Filtering

I have 2 worksheet that has some rows highlighted.  I need to take the highlighted rows from these 2 worksheet and combine them into one.  

I looked at the conditional formatting to see if there's a way to do this but I can't to get it down.  Is there a way to do this?  

I need to pull all highlighted rows from sheet1 and sheet2 and combine into sheet3.  Then I need to check column A to make sure that there's no duplicate.  Any help is appreciated.
0
holemania
Asked:
holemania
  • 4
  • 3
1 Solution
 
SiddharthRoutCommented:
May I see a sample File? and how the output should look like?

Sid
0
 
holemaniaAuthor Commented:
Here's an example.  Have 2 users with 2 different worksheet.  I need to pull items that they highlight from each list and then maybe mark if it's got duplicate? Example.xls
0
 
SiddharthRoutCommented:
Ok the code is almost done. Quick question.

Will there be only 4 coulmns always?

Sid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SiddharthRoutCommented:
Or I'll rephrase my question. What would you consider a duplicate.

Same Name OR
Same name + Same Department OR
Same name + Same Department + Same Title

Sid
0
 
SiddharthRoutCommented:
Is this what you want?

Run the Macro Sample. the results will be populated in sheets "Final Result No Duplicate"

Sid

Code Used

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long, ws3LastRow As Long
    Dim Rng As Range
    
    Set ws1 = Sheets("Final Result No Duplicate")
    Set ws2 = Sheets("Sandy List")
    Set ws3 = Sheets("Mark List")
    
    ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row
    ws3LastRow = ws3.Range("A" & Rows.Count).End(xlUp).Row
    
    With ws2
        For i = 2 To ws2LastRow
            If .Range("A" & i).Interior.ColorIndex > 0 Then
                If Rng Is Nothing Then
                    Set Rng = .Rows(i)
                Else
                    Set Rng = Union(Rng, .Rows(i))
                End If
            End If
        Next
    End With
    
    If Not Rng Is Nothing Then
        Rng.Copy
        ws1.Range("A" & ws1LastRow).PasteSpecial xlPasteAll
        ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
    
    Set Rng = Nothing
    
    With ws3
        For i = 2 To ws3LastRow
            If .Range("A" & i).Interior.ColorIndex > 0 Then
                If Rng Is Nothing Then
                    Set Rng = .Rows(i)
                Else
                    Set Rng = Union(Rng, .Rows(i))
                End If
            End If
        Next
    End With
    
    If Not Rng Is Nothing Then
        Rng.Copy
        ws1.Range("A" & ws1LastRow).PasteSpecial xlPasteAll
        ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
    
    With ws1
        .Columns("A:D").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        For i = ws1LastRow To 2 Step -1
            If .Range("B" & i).Value = .Range("B" & i - 1).Value And _
            .Range("C" & i).Value = .Range("C" & i - 1).Value And _
            .Range("D" & i).Value = .Range("D" & i - 1).Value Then
                .Rows(i).Delete shift:=xlUp
            End If
        Next i
    End With
End Sub

Open in new window

Example-2-.xls
0
 
holemaniaAuthor Commented:
Thanks I will take a look.  There's more to copy over and not just the 4 column.  I only use that to simplify what I was trying to get at.  

The field to look for duplicate is only the ID.  If the ID is the same, then it's a duplicate and just use one of the 2.
0
 
holemaniaAuthor Commented:
Awesome.  Thank you!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now