Solved

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

Posted on 2016-08-04
13
32 Views
Last Modified: 2016-08-27
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
Comment
Question by:Jamie M
  • 7
  • 5
13 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41742881
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
 

Expert Comment

by:Davidjevans
ID: 41742964
Have you tried the MS Excel add-in "Fuzzy Lookup"?
0
 

Author Comment

by:Jamie M
ID: 41743993
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
 

Author Comment

by:Jamie M
ID: 41743994
Davidjevans

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

Jamie
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41744039
Hi Jamie,

You forgot to attache the sample workbook.
0
 

Author Comment

by:Jamie M
ID: 41744105
Neeraj...  

hmmm... I attached it but I think I forgot to actually upload it  :)  Sorry!  Here it is
Example-Workbook.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41744217
In case 2 and 3, copy the values from which worksheet into worksheet4 and worksheet5?
Also does Sheet1 not have headers?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41744285
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
 

Author Comment

by:Jamie M
ID: 41746850
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points (awarded by participants)
ID: 41747057
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
 

Author Comment

by:Jamie M
ID: 41747139
Neeraj-

Your fix was perfect!  Thank you so much!

Jamie
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41747161
You're welcome Jamie! Glad to help.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41772810
The best chosen answer resolved the question.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now