compare column A in WS1 with Column A in WS2. If match then copy to WS3. If no match then copy to "NotFound".

N

nguy0279

New Member
Joined
Jul 29, 2014
Messages
5
I have been trying to finish this macro. I have two sheets that will compare column A in WS1 with Column A in WS2. If match then copy to WS3. If no match then copy to "NotFound". The script works when the to columns equal each other. However, when the two columns do not match it will copy multiple times into the "NotFound" sheet. I understand that it is because of the loop, in that every time it runs through the code it will find the Non-matched value again. Hops this made sense. I have included the code below. I am new to VB so could use the help. Thank you.

WS 1

AB
9781847320

<colgroup><col></colgroup><tbody>
</tbody>
989

<colgroup><col></colgroup><tbody>
</tbody>
9781780971

<colgroup><col></colgroup><tbody>
</tbody>
6561

<colgroup><col></colgroup><tbody>
</tbody>
978162772

<colgroup><col></colgroup><tbody>
</tbody>
CNVSR-C

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>


WS2
AB
4560220550

<colgroup><col></colgroup><tbody>
</tbody>
1111
9781780971MPE6561
9781847322MBO989
45602205531111

<tbody>
</tbody>


"NotFound" WS
45602205531111

<tbody>
</tbody>

45602205531111

<tbody>
</tbody>

45602205531111

<tbody>
</tbody>


Dim numRowsN As Long


Dim counterOriginal As Integer
Dim counterN As Integer
Dim counterSheet3 As Integer
Dim OriginalValue As String
Dim NValue As String
Dim counterNotFound As Integer




counterSheet3 = 2




Sheets("Sheet3").Columns("A").NumberFormat = "@"
numRowsOriginal = ActiveWorkbook.Worksheets("WS1").Range("A2",Worksheets("WS1").Range("A2").End(xlDown)).Rows.Count

numRowsN=ActiveWorkbook.Worksheets("WS2").Range("A2",Worksheets("WS2").Range("A2").End(xlDown)).Rows.Count





Sheets("WS1").Rows(1).Copy Sheets("Sheet3").Rows(1)
For counterOriginal = 2 To numRowsOriginal


For counterN = 2 To numRowsN + 1

OriginalValue = Sheets("WS1").Cells(counterOriginal, 1).Value
NValue = Sheets("N").Cells(counterN, 1).Value
If OriginalValue = NalpacValue Then


Sheets("WS1l").Rows(counterOriginal).Copy Sheets("Sheet3").Rows(counterSheet3)

'copy Manufacturer number
Sheets("WS2").Cells(counterN, "E").Copy Sheets("Sheet3").Cells(counterSheet3, "FG")

'copy N item number
Sheets("WS2").Cells(counterN, "B").Copy Sheets("Sheet3").Cells(counterSheet3, "FH")

'copy N price
Sheets("WS2").Cells(counterN, "G").Copy Sheets("Sheet3").Cells(counterSheet3, "FI")


'copy Qty
Sheets("WS2").Cells(counterN, "K").Copy Sheets("Sheet3").Cells(counterSheet3, "FJ")
counterSheet3 = counterSheet3 + 1

' ElseIf OriginaValue <> NValue Then
' Sheets("WS1").Rows(counterOriginal).Copy Sheets("NotFound").Rows(counterSheet3)
' counterNotFouind = counterNotFound + 1
End If






Next
Next
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
nguy0279,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


I assume that all 4 worksheets, WS1, WS2, WS3, and, NotFound, already exist.

And, if I understand you correctly.


Sample worksheets:


Excel 2007
AB
19781847320989
297817809716561
3978162772CNVSR-C
4
WS1



Excel 2007
AB
145602205501111
29781780971MPE6561
39781847322MBO989
445602205531111
5
WS2



Excel 2007
AB
1
2
3
WS3



Excel 2007
AB
1
2
3
4
5
6
NotFound


After the macro in worksheets WS3, and, NotFound:


Excel 2007
AB
197817809716561
29781780971MPE6561
3
WS3



Excel 2007
AB
19781847320989
2978162772CNVSR-C
345602205501111
49781847322MBO989
545602205531111
6
NotFound


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub Compare_WS1_WS2()
' hiker95, 07/29/2014, ME795109
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet, wn As Worksheet
Dim c As Range, arng As Range, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("WS1")
Set w2 = Sheets("WS2")
Set w3 = Sheets("WS3")
Set wn = Sheets("NotFound")
With w1
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set arng = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If arng Is Nothing Then
      nr = wn.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And wn.Cells(1, 1) = "" Then nr = 1
      wn.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
    ElseIf Not arng Is Nothing Then
      nr = w3.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w3.Cells(1, 1) = "" Then nr = 1
      w3.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
      Set arng = Nothing
    End If
  Next c
End With
With w2
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set arng = w1.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If arng Is Nothing Then
      nr = wn.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And wn.Cells(1, 1) = "" Then nr = 1
      wn.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
    ElseIf Not arng Is Nothing Then
      nr = w3.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w3.Cells(1, 1) = "" Then nr = 1
      w3.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
      Set arng = Nothing
    End If
  Next c
End With
wn.Columns(1).Resize(, 2).AutoFit
With w3
  .Columns(1).Resize(, 2).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Compare_WS1_WS2 macro.
 
Upvote 0
hiker95 said:
nguy0279,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


I assume that all 4 worksheets, WS1, WS2, WS3, and, NotFound, already exist.

And, if I understand you correctly.


Sample worksheets:

Excel 2007
AB
19781847320989
297817809716561
3978162772CNVSR-C
4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
WS1​



Excel 2007
AB
145602205501111
29781780971MPE6561
39781847322MBO989
445602205531111
5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
WS2​



Excel 2007
AB
1
2
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
WS3​



Excel 2007
AB
1
2
3
4
5
6

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
NotFound​



After the macro in worksheets WS3, and, NotFound:

Excel 2007
AB
197817809716561
29781780971MPE6561
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
WS3​



Excel 2007
AB
19781847320989
2978162772CNVSR-C
345602205501111
49781847322MBO989
545602205531111
6

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
NotFound​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub Compare_WS1_WS2()
' hiker95, 07/29/2014, ME795109
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet, wn As Worksheet
Dim c As Range, arng As Range, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("WS1")
Set w2 = Sheets("WS2")
Set w3 = Sheets("WS3")
Set wn = Sheets("NotFound")
With w1
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set arng = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If arng Is Nothing Then
      nr = wn.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And wn.Cells(1, 1) = "" Then nr = 1
      wn.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
    ElseIf Not arng Is Nothing Then
      nr = w3.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w3.Cells(1, 1) = "" Then nr = 1
      w3.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
      Set arng = Nothing
    End If
  Next c
End With
With w2
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set arng = w1.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If arng Is Nothing Then
      nr = wn.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And wn.Cells(1, 1) = "" Then nr = 1
      wn.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
    ElseIf Not arng Is Nothing Then
      nr = w3.Cells(Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w3.Cells(1, 1) = "" Then nr = 1
      w3.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
      Set arng = Nothing
    End If
  Next c
End With
wn.Columns(1).Resize(, 2).AutoFit
With w3
  .Columns(1).Resize(, 2).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Compare_WS1_WS2 macro.
Click to expand...



Thank you for your help. I made some changes to the script but am still having problems. Here are the things that are needed.

1. Match column A of WS1 with Column A WS2
2. IF match copy entire row from WS1 to WS3(matched sheet)
3. If not matched copy entire row from WS2 to WN(Not found)
4. Column B does not matter.

Problems after running the code
1. Items that matched are in both WS3 and WN: 5380113964, 5390113909,5380113902
2. Some items seemed to had been skipped or not processed: H6581125680,444444,8258102445, 3912104899

I have also added some comments to your code. I hope I understood it right. If I am wrong please correct me. The main two lines that I added to copy the entire rows have an arrow next to it.

w3.Range("A1:DS5000").Clear
wn.Range("A1:DS5000").Clear
'w3.Columns("A").NumberFormat = "@"
'wn.Columns("A").NumberFormat = "@"
w1.Rows(1).Copy wn.Rows(1)
w2.Rows(1).Copy w3.Rows(1)
With w1
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
' MsgBox "C valueis: " & c

'Matching w1 value with w2 value
Set arng = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
'IF not matched in W1 and W2
If arng Is Nothing Then
nr = wn.Cells(Rows.Count, "A").End(xlUp).Row + 1
' if W2 cell is empty
If nr = 2 And wn.Cells(1, 1) = "" Then nr = 1
'Not matched is copied to "NotFound"
w1.Rows(nr).Copy wn.Rows(nr) <---------
'wn.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
' Matched found in W1 and W2
ElseIf Not arng Is Nothing Then
'counts the number rows in W3
nr = w3.Cells(Rows.Count, "A").End(xlUp).Row + 1
' W3 row A is not empty
If nr = 2 And w3.Cells(1, 1) = "" Then nr = 1
'copy matched w1 to W3

w2.Rows(nr).Copy w3.Rows(nr) <-------
'w3.Cells(nr, 1).Resize(, 2).Value = c.Resize(, 2).Value
Set arng = Nothing
End If
Next c
End With


WS1
UPCSKU
2222222
33333333
5380113964

<colgroup><col></colgroup><tbody>
</tbody>
8707
5380113902

<colgroup><col></colgroup><tbody>
</tbody>
9137
5390113909

<colgroup><col></colgroup><tbody>
</tbody>
8640
5380113926

<colgroup><col></colgroup><tbody>
</tbody>
9138
5390113923

<colgroup><col></colgroup><tbody>
</tbody>
8641
H6581125680

<colgroup><col></colgroup><tbody>
</tbody>
8458
444444

<colgroup><col></colgroup><tbody>
</tbody>
8889
11111111111

<colgroup><col></colgroup><tbody>
</tbody>
8887
8258102445

<colgroup><col></colgroup><tbody>
</tbody>
0132
3912104899

<colgroup><col></colgroup><tbody>
</tbody>
0083

<tbody>
</tbody>


WS3 (matched)
UPC

<colgroup><col></colgroup><tbody>
</tbody>
SKU
5390113909

<colgroup><col></colgroup><tbody>
</tbody>
11390B
5380113926

<colgroup><col></colgroup><tbody>
</tbody>
11392
5390113923

<colgroup><col></colgroup><tbody>
</tbody>
11393b
5380113964

<colgroup><col></colgroup><tbody>
</tbody>
11396
5380113995

<colgroup><col></colgroup><tbody>
</tbody>
1139
5380113902

<colgroup><col></colgroup><tbody>
</tbody>
11390

<tbody>
</tbody>



WN(Not Found)
UPCSKU
22222222
33333333
5380113964

<colgroup><col></colgroup><tbody>
</tbody>
48707
5380113902

<colgroup><col></colgroup><tbody>
</tbody>
49137
5390113909

<colgroup><col></colgroup><tbody>
</tbody>
48640
5380113926

<colgroup><col></colgroup><tbody>
</tbody>
49138

<tbody>
</tbody>
 
Upvote 0
nguy0279,

QUOTES
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

I will be back later to work with your new dataset.
 
Upvote 0
Thank you very much, Hiker95. I really appreciate the help. I hope this is clearer now.

WS2
UPCSKU
5380113902

<colgroup><col></colgroup><tbody>
</tbody>
BN11390
5390113909

<colgroup><col></colgroup><tbody>
</tbody>
BN13908
H6581125680

<colgroup><col></colgroup><tbody>
</tbody>
8458
5380113926

<colgroup><col></colgroup><tbody>
</tbody>
BN11392B
5390113923

<colgroup><col></colgroup><tbody>
</tbody>
BN11396
5380113964

<colgroup><col></colgroup><tbody>
</tbody>
BN11399
5380113995

<colgroup><col></colgroup><tbody>
</tbody>
BN11399
5380122201

<colgroup><col></colgroup><tbody>
</tbody>
BN12220
5380122225

<colgroup><col></colgroup><tbody>
</tbody>
BN12230

<tbody>
</tbody>


The output should be for WS3 and WN:

WS3 (Matched): The rows are copied from WS2
UPCSKU
5380113902

<colgroup><col></colgroup><tbody>
</tbody>
BN11390
5390113909

<colgroup><col></colgroup><tbody>
</tbody>
BN13908
H6581125680

<colgroup><col></colgroup><tbody>
</tbody>
8458
5380113926

<colgroup><col></colgroup><tbody>
</tbody>
BN11392B
5390113923

<colgroup><col></colgroup><tbody>
</tbody>
BN11396
5380113964

<colgroup><col></colgroup><tbody>
</tbody>
BN11399
5380113995

<colgroup><col></colgroup><tbody>
</tbody>
BN11399

<tbody>
</tbody>

WN(not found): The row is copied from WS1
444444

<tbody>
</tbody>

8889
11111111111

<tbody>
</tbody>

8887
8258102445

<tbody>
</tbody>

0132
3912104899

<tbody>
</tbody>

0083

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
nguy0279,

Thank you very much, Hiker95. I really appreciate the help. I hope this is clearer now.
Click to expand...

No.


One last try.

In your next reply, please include screenshots of ALL 4 worksheets, with their correct worksheet names.
 
Upvote 0
 
Last edited:
Upvote 0
nguy0279,

You have changed what your raw data worksheet(s) look like four times.

The links to your png/grahics/pictures will not do.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
Hiker95,

Thanks for the help. Sorry for such confusion. I was just trying to make it simple but i guess i made it more difficult. Thank though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
308
B-radK
B
D
  • Solved
VBA copying entire rows and over writing destination formatting
Replies
3
Views
140
Alex Blakenburg
A
P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
354
Tupe77
T
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
810
Joe4
Joe4
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
10
Views
790
kevin9999
K

Forum statistics

Threads
1,216,101
Messages
6,128,838
Members
449,471
Latest member
lachbee

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