Go Premium for a chance to win a PS4. Enter to Win

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

Compare two columns in two worksheets, copy like data to third sheet and mismatched data to fourth and fifth sheet

I need VBA to compare values in column B and column A in worksheet 1 to values in column B and column A in worksheet 2. If the same values are present in both columns of both worksheets, copy the full rows into worksheet 3. If values in both columns are present in worksheet 1 but not in worksheet 2, copy the full rows into worksheet 4. If values in both columns are present in worksheet 2 but not in worksheet 1, copy the full rows into worksheet 5.
0
Jamie M
Asked:
Jamie M
  • 7
  • 5
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Can you upload a small sample workbook with max 25-30 rows of data to know the sheet structure?
Haw many rows of data is there on both the sheets in actual workbook?
0
 
DavidjevansCommented:
Have you tried the MS Excel add-in "Fuzzy Lookup"?
0
 
Jamie MAuthor Commented:
Neeraj

Thank you for your help! I have attached a small example of what the workbook would look like - I only put data in Columns A and B of Sheet1 and Sheet2 but my actual workbook will have several columns filled. The number of rows in each sheet will be different each day.

Jamie
0
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.

 
Jamie MAuthor Commented:
Davidjevans

No I have not tried Fuzzy Lookup but I will check it out today!  Thank you,

Jamie
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Jamie,

You forgot to attache the sample workbook.
0
 
Jamie MAuthor Commented:
Neeraj...  

hmmm... I attached it but I think I forgot to actually upload it  :)  Sorry!  Here it is
Example-Workbook.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In case 2 and 3, copy the values from which worksheet into worksheet4 and worksheet5?
Also does Sheet1 not have headers?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this...

In the attached, click the button on Sheet1 to run the code.
If you have any issue with downloading and opening the attached workbook due to a temporary bug in the forum, first download and save it on your system and then open it.

Sub MatchAndCopy()
Dim x, y, Arr3(), Arr4(), Arr5()
Dim dict As Object, dict2 As Object
Dim i As Long, j As Long, k As Long, l As Long, m As Long

Set dict = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")

x = Sheet1.Range("A1").CurrentRegion.Value
y = Sheet2.Range("A1").CurrentRegion.Value

ReDim Preserve Arr3(1 To UBound(x, 1), 1 To UBound(x, 2))
ReDim Preserve Arr4(1 To UBound(x, 1), 1 To UBound(x, 2))
ReDim Preserve Arr5(1 To UBound(y, 1), 1 To UBound(y, 2))

Sheet3.Cells.Clear
Sheet4.Cells.Clear
Sheet5.Cells.Clear
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1) & x(i, 2)) = ""
Next i

For i = 1 To UBound(y, 1)
    dict2.Item(y(i, 1) & y(i, 2)) = ""
Next i

For j = 1 To UBound(y, 1)
    If dict.exists(y(j, 1) & y(j, 2)) Then
        k = k + 1
        Arr3(k, 1) = y(j, 1)
        Arr3(k, 2) = y(j, 2)
    ElseIf Not dict.exists(y(j, 1) & y(j, 2)) Then
        l = l + 1
        Arr5(l, 1) = y(j, 1)
        Arr5(l, 2) = y(j, 2)
    End If
Next j
k = 0
For j = 1 To UBound(x, 1)
    If Not dict2.exists(x(j, 1) & x(j, 2)) Then
        k = k + 1
        Arr4(k, 1) = x(j, 1)
        Arr4(k, 2) = x(j, 2)
    End If
Next j
Sheet3.Range("A1").Resize(UBound(Arr3), 2).Value = Arr3
Sheet4.Range("A1").Resize(UBound(Arr4), 2).Value = Arr4
Sheet5.Range("A1").Resize(UBound(Arr5), 2).Value = Arr5
Set dict = Nothing
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

Example-Workbook.xlsm
0
 
Jamie MAuthor Commented:
Neeraj-

This works great to separate everything! However, my real data has several columns and the code only places columns A and B into the three new worksheets... I need the entire row, all columns, to be transferred. Also, all five sheets should have matching headers. Sheet one should have the same headers as sheet two etc.  It looks like the code is placing headers only in sheet five and I must have forgotten them on sheet one.

Jamie
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with the tweaked code.
Sheet3, Sheet4 and Sheet5 contain the headers in advance and the data will be pasted below those headers.
Example-Workbook-v2.xlsm
0
 
Jamie MAuthor Commented:
Neeraj-

Your fix was perfect!  Thank you so much!

Jamie
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Jamie! Glad to help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The best chosen answer resolved the question.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now