VBA Find Non-Match Criteria in WS2 and copy Criteria to WS1

CyrusTheVirus

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
749
Office Version
  1. 365
Platform
  1. Windows
Hi, I have two worksheets. I need to find Employee ID's in "Sheet2" that match the Employee ID's in "Sheet1". If "Sheet2" has an Employee ID that does not match an Employee ID in "Sheet1", then I need to copy specific cells from the said row of "Sheet2" over to "Sheet1".


On top of that, when copying over, I need to make sure that a whole row inserts for the copied cells in order for the previous amounts to be in the correct spot.


Example:[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]State[/TD]
[TD]Employee Name[/TD]
[TD]Employee ID[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Variance
[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[TD]-45[/TD]
[TD]20[/TD]
[TD]-15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]35[/TD]
[TD]-37.5[/TD]
[TD]37.5[/TD]
[TD]-35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]20[/TD]
[TD]-25[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]Roberts, Chad[/TD]
[TD]9799[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Employee[/TD]
[TD]Employee ID[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Carter, Randy[/TD]
[TD]4112[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]NH[/TD]
[TD]Torres, Jose[/TD]
[TD]8884[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

After finding the non-matches, bring the non-match items from Column A to C from 'Sheet2' over to 'Sheet1' to look like this (since they did not match):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]State[/TD]
[TD]Employee Name[/TD]
[TD]Employee ID[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Variance
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[TD]-45[/TD]
[TD]20[/TD]
[TD]-15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]35[/TD]
[TD]-37.5[/TD]
[TD]37.5[/TD]
[TD]-35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MD[/TD]
[TD]Carter, Randy[/TD]
[TD]4112[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NH[/TD]
[TD]Torres, Jose[/TD]
[TD]8884[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]20[/TD]
[TD]-25[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MA[/TD]
[TD]Roberts, Chad[/TD]
[TD]9799[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

My code so far...
Code: 
Option Explicit


Sub CopyNonMatches()


Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws1Range As Range, ws2range As Range
Dim ws1Long As Long, ws2long As Long


Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")


With ws1
ws1Long = .Range("C" & .Rows.Count).End(xlUp).Row
End With


Set ws1Range = ws1.Range("C3", "C" & ws1Long)


With ws2
ws2long = .Range("C" & .Rows.Count).End(xlUp).Row
End With


Set ws2range = ws2.Range("C3", "C" & ws2long)

'Now I need to compare the ranges 'ws1Range' with 'ws2Range' and if 'ws2Range' has ID's that...
'...are not included in 'ws1Range', then I need to copy those EE's info over to 'Sheet1'.


???????????????????


End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Are you trying to duplicate the row order of Sheet2 on Sheet1? If yes, then in your example Sheet1 end result, are rows 6 and 7 swapped? Otherwise, I don't follow how the rows from sheet2 are ordered on sheet1.
 
Upvote 0
@ AlphaFrog

Hi, well, I wasn't sure the easiest way to get the cells from sheet2 to sheet1. I figured I could sort them afterwards alphabetically or by state.

My main thing is trying to figure out which ID's in Sheet 2 are not in Sheet 1, After finding the ID's with NO match, then copy specific cells over to sheet 1.

Example: Coleman's ID isn't on Sheet 1, so I need to copy his 'state' 'name' and 'id' over to sheet 1 in that same order, excluding his 'Amount'.

I know what you're getting at though, the way they are placed there looks odd, but I can simply fill the blanks in with a '0' and re-sort the names to appear more unison... Does that help?

If you need further clarification, just let me know. Very frustrated on this part. Can't figure it out.
 
Upvote 0
CyrusTheVirus said:
@ AlphaFrog

Hi, well, I wasn't sure the easiest way to get the cells from sheet2 to sheet1. I figured I could sort them afterwards alphabetically or by state.

My main thing is trying to figure out which ID's in Sheet 2 are not in Sheet 1, After finding the ID's with NO match, then copy specific cells over to sheet 1.

Example: Coleman's ID isn't on Sheet 1, so I need to copy his 'state' 'name' and 'id' over to sheet 1 in that same order, excluding his 'Amount'.

I know what you're getting at though, the way they are placed there looks odd, but I can simply fill the blanks in with a '0' and re-sort the names to appear more unison... Does that help?

If you need further clarification, just let me know. Very frustrated on this part. Can't figure it out.
Click to expand...

So paste the non-matched IDs (columns A,B,C) at the bottom on sheet1?

I understand what you want to copy\paste. I do not follow what you mean by this...
I know what you're getting at though, the way they are placed there looks odd, but I can simply fill the blanks in with a '0' and re-sort the names to appear more unison...
Click to expand...
 
Upvote 0
@ AlphaFrog

My apologies. It's very difficult to explain this one without actually taking a screenshot of the spreadsheets.

AlphaFrog said:
So paste the non-matched IDs (columns A,B,C) at the bottom on sheet1?

Well, I have a formula on the bottom row so that probably wouldn't work...

I need a code to paste the non-matched ID's (columns A,B,C) to Row 8 of 'Sheet1'???
Click to expand...
 
Last edited:
Upvote 0
CyrusTheVirus said:
I need a code to paste the non-matched ID's (columns A,B,C) to Row 8 of 'Sheet1'???
Click to expand...

Code: 
[color=darkblue]Sub[/color] CopyNonMatches()
    
    [color=darkblue]Dim[/color] ws1 [color=darkblue]As[/color] Worksheet, ws2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] vIDs1 [color=darkblue]As[/color] [color=darkblue]Variant[/color], vData [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] ws1 = ThisWorkbook.Sheets("Sheet1")
    [color=darkblue]Set[/color] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    vIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp)).Value
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vData, 1)
        [color=darkblue]If[/color] IsError(Application.Match(vData(i, 3), vIDs1, 0)) [color=darkblue]Then[/color]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] CopyNonMatches()
    
    [COLOR=darkblue]Dim[/COLOR] ws1 [COLOR=darkblue]As[/COLOR] Worksheet, ws2 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] vIDs1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], vData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws1 = ThisWorkbook.Sheets("Sheet1")
    [COLOR=darkblue]Set[/COLOR] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    vIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp)).Value
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
        [COLOR=darkblue]If[/COLOR] IsError(Application.Match(vData(i, 3), vIDs1, 0)) [COLOR=darkblue]Then[/COLOR]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

@ AlphaFrog,

THANK YOU!!! THAT IS AWESOME!!!! TOO COOL!!!! Works Perfectly!

Now I just need to figure out what you did haha. Never used a variant or UBound before. Just started using VBA a month ago.

Seriously, thank you for being patient with me!!! Really appreciate it!!!
 
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] CopyNonMatches()
    
    [COLOR=darkblue]Dim[/COLOR] ws1 [COLOR=darkblue]As[/COLOR] Worksheet, ws2 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] vIDs1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], vData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws1 = ThisWorkbook.Sheets("Sheet1")
    [COLOR=darkblue]Set[/COLOR] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    vIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp)).Value
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
        [COLOR=darkblue]If[/COLOR] IsError(Application.Match(vData(i, 3), vIDs1, 0)) [COLOR=darkblue]Then[/COLOR]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...


I know this is probably asking to much now, but say there were duplicates in 'Sheet2', how could you get the code to pull the info only one time and not both (or multiple) times?

For instance, say Anthony Coleman is mentioned twice in Sheet2 like this:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466
[/TD]
[/TR]
</tbody>[/TABLE]

Anthony Coleman, then gets copied twice into 'Sheet1'. How can I get it so the code only pulls Anthony one time and not two or three, or however many duplicates there are?
 
Upvote 0
CyrusTheVirus said:
How can I get it so the code only pulls Anthony one time and not two or three, or however many duplicates there are?
Click to expand...

Code: 
[color=darkblue]Sub[/color] CopyNonMatches()
    
    [color=darkblue]Dim[/color] ws1 [color=darkblue]As[/color] Worksheet, ws2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rngIDs1 [color=darkblue]As[/color] Range, vData [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] ws1 = ThisWorkbook.Sheets("Sheet1")
    [color=darkblue]Set[/color] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    [color=darkblue]Set[/color] rngIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp))
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vData, 1)
        [color=darkblue]If[/color] IsError(Application.Match(vData(i, 3), rngIDs1, 0)) [color=darkblue]Then[/color]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] CopyNonMatches()
    
    [COLOR=darkblue]Dim[/COLOR] ws1 [COLOR=darkblue]As[/COLOR] Worksheet, ws2 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rngIDs1 [COLOR=darkblue]As[/COLOR] Range, vData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws1 = ThisWorkbook.Sheets("Sheet1")
    [COLOR=darkblue]Set[/COLOR] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    [COLOR=darkblue]Set[/COLOR] rngIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp))
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
        [COLOR=darkblue]If[/COLOR] IsError(Application.Match(vData(i, 3), rngIDs1, 0)) [COLOR=darkblue]Then[/COLOR]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

@ AlphaFrog

Hm, Unfortunately that one is not working :( It is still pulling duplicates.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
373
Alex Blakenburg
A
B
  • Question Question
Lookup and match reference in other WS
2
Replies
12
Views
1K
Aladin Akyurek
Aladin Akyurek
V
  • Question Question
Match Index
Replies
3
Views
1K
Legacy 436357
L
R
  • Question Question
VBA to find the exception and paste the matched records to the new sheet
Replies
1
Views
600
Rabiyudeen
R
M
  • Question Question
VBA code to match id and Date from sheet1 with Sheet 2 and copy matching rows and unmatched with blank
Replies
5
Views
587
motherindia
M

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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