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

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781847320[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]989[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781780971[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]6561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]978162772[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CNVSR-C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


WS2
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 153"]
<colgroup><col></colgroup><tbody>[TR]
[TD]4560220550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1111[/TD]
[/TR]
[TR]
[TD]9781780971[/TD]
[TD]MPE6561[/TD]
[/TR]
[TR]
[TD]9781847322[/TD]
[TD]MBO989[/TD]
[/TR]
[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]


"NotFound" WS
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]


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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]9781847320[/TD]
[TD="bgcolor: #FFFFFF, align: right"]989[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]9781780971[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6561[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]978162772[/TD]
[TD="bgcolor: #FFFFFF"]CNVSR-C[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
WS1



Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]4560220550[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1111[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]9781780971[/TD]
[TD="bgcolor: #FFFFFF"]MPE6561[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]9781847322[/TD]
[TD="bgcolor: #FFFFFF"]MBO989[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]4560220553[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1111[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
WS2



Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
WS3



Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
NotFound



After the macro in worksheets WS3, and, NotFound:

Excel 2007
AB
9781780971
9781780971MPE6561

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]6561[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
WS3



Excel 2007
AB
9781847322MBO989
4560220553

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]9781847320[/TD]
[TD="bgcolor: #FFFFFF, align: right"]989[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]978162772[/TD]
[TD="bgcolor: #FFFFFF"]CNVSR-C[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]4560220550[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1111[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1111[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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.



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
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8707[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113902[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: -webkit-right"]9137[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8640[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113926[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9138[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113923[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8641[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD]H6581125680[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8458[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]444444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8889[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]11111111111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8887[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]8258102445[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0132[/TD]
[/TR]
[TR]
[TD][TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3912104899[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0083[/TD]
[/TR]
</tbody>[/TABLE]


WS3 (matched)
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]UPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11390B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113926[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11392[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113923[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11393b[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11396[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1139[/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113902
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11390[/TD]
[/TR]
</tbody>[/TABLE]



WN(Not Found)
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]2222[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]48707[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113902[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]49137[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]48640[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113926[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]49138[/TD]
[/TR]
</tbody>[/TABLE]
 
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
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113902[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11390[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN13908[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]H6581125680[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8458[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113926[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11392B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113923[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11396[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11399[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11399[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380122201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN12220[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380122225[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN12230[/TD]
[/TR]
</tbody>[/TABLE]


The output should be for WS3 and WN:

WS3 (Matched): The rows are copied from WS2
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113902[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11390[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN13908[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]H6581125680[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8458[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113926[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11392B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5390113923[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11396[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11399[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5380113995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BN11399[/TD]
[/TR]
</tbody>[/TABLE]

WN(not found): The row is copied from WS1
[TABLE="class: cms_table_grid, width: 150"]
<tbody>[TR]
[TD][TABLE="class: cms_table_grid, width: 150"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 117"]
<tbody>[TR]
[TD="align: right"]444444[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]8889[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 117"]
<tbody>[TR]
[TD="align: right"]11111111111[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]8887[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 117"]
<tbody>[TR]
[TD="align: right"]8258102445[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]0132[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 117"]
<tbody>[TR]
[TD="align: right"]3912104899[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]0083[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
nguy0279,

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

No.


One last try.

In your next reply, please include screenshots of ALL 4 worksheets, with their correct worksheet names.
 
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

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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