Compare two columns of two sheets and insert row if new data found

I

Itzybell

New Member
Joined
Jul 15, 2019
Messages
7
Hey everyone! This is my first time posting here :)
I've been scrolling through the forum for a solution to my problem but none seems to work!

I've just recently started doing macros and
I have a macro that would compare two excel sheets Col A(Sheet 1 and Sheet 2) and output the new data found in sheet 2 to the bottom of sheet 1 data:

Sheet 1: [TABLE="width: 165"]
<tbody>[TR]
[TD]No.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]A100[/TD]
[TD]Obj1[/TD]
[/TR]
[TR]
[TD]A300[/TD]
[TD]Obj3[/TD]
[/TR]
[TR]
[TD]A400[/TD]
[TD]Obj4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]No.[/TD]
[TD="class: xl66, width: 64"]Name[/TD]
[/TR]
[TR]
[TD="class: xl67"]A100[/TD]
[TD="class: xl67"]Obj1[/TD]
[/TR]
[TR]
[TD="class: xl67"]A200[/TD]
[TD="class: xl67"]Obj2[/TD]
[/TR]
[TR]
[TD="class: xl67"]A300[/TD]
[TD="class: xl67"]Obj3[/TD]
[/TR]
[TR]
[TD="class: xl67"]A400[/TD]
[TD="class: xl67"]Obj4[/TD]
[/TR]
[TR]
[TD="class: xl67"]A500[/TD]
[TD="class: xl67"]Obj5[/TD]
[/TR]
[TR]
[TD="class: xl67"]A600[/TD]
[TD="class: xl67"]Obj6[/TD]
[/TR]
</tbody>[/TABLE]

OUTPUT(Sheet 1):
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]No.[/TD]
[TD="class: xl66, width: 64"]Name[/TD]
[/TR]
[TR]
[TD="class: xl67"]A100[/TD]
[TD="class: xl67"]Obj1[/TD]
[/TR]
[TR]
[TD="class: xl67"]A300[/TD]
[TD="class: xl67"]Obj3[/TD]
[/TR]
[TR]
[TD="class: xl67"]A400[/TD]
[TD="class: xl67"]Obj4[/TD]
[/TR]
[TR]
[TD="class: xl68"]A200[/TD]
[TD="class: xl68"]Obj2[/TD]
[/TR]
[TR]
[TD="class: xl68"]A500 [/TD]
[TD="class: xl68"]Obj5[/TD]
[/TR]
[TR]
[TD="class: xl68"]A600[/TD]
[TD="class: xl68"]Obj6[/TD]
[/TR]
[TR]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]
The problem is that now I wish to insert the new data to Sheet 1 by inserting new rows in between Sheet 1's data after comparison automatically so I get the overview of where the new data is:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]No.[/TD]
[TD="class: xl66, width: 64"]Name[/TD]
[/TR]
[TR]
[TD="class: xl67"]A100[/TD]
[TD="class: xl67"]Obj1[/TD]
[/TR]
[TR]
[TD="class: xl68"]A200[/TD]
[TD="class: xl68"]Obj2[/TD]
[/TR]
[TR]
[TD="class: xl67"]A300[/TD]
[TD="class: xl67"]Obj3[/TD]
[/TR]
[TR]
[TD="class: xl67"]A400[/TD]
[TD="class: xl67"]Obj4[/TD]
[/TR]
[TR]
[TD="class: xl68"]A500 [/TD]
[TD="class: xl68"]Obj5[/TD]
[/TR]
[TR]
[TD="class: xl68"]A600[/TD]
[TD="class: xl68"]Obj6[/TD]
[/TR]
</tbody>[/TABLE]

Is there a macro that will be able to do this automatically?
Thank you so much for your help!
I've been really troubled over this.
 
Sort by date Sort by votes
@Itzybell
Not sure I understand what you are saying, can you please show an example of what is not working.
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Fluff!

To explain in detail,
Im actually working on a BOM excel sheet,
Sheet 1 being the main BOM sheet and sheet 2 being the exported

All parts refer back to a level which was why the macro was needed to insert rows in between.
When I tried the macro on a sample data sheet,with Sheet 2 being a duplicate of Sheet 1 with extra rows added in between. (As shown in Sheet 2 bold red),
the macro works perfectly.

Sheet 1:
[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item Id[/TD]
[TD]Description[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Base[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A34763[/TD]
[TD]Lid[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Bolt[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Bolt[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A84578[/TD]
[TD]Nut[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 305"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item Id[/TD]
[TD]Description[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Base[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A45873[/TD]
[TD]Lid 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A34623[/TD]
[TD]Plug[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Bolt[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Output:
[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item Id[/TD]
[TD]Description[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Base[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A45873[/TD]
[TD]Lid 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A34623[/TD]
[TD]Plug[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A34763[/TD]
[TD]Lid[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Bolt[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Bolt[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A84578[/TD]
[TD]Nut[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

However, when I try it on my actual BOM file,
the output seems to become like this:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Item Id[/TD]
[TD="class: xl67"]Description[/TD]
[TD="class: xl67"]Level[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A10256[/TD]
[TD="class: xl67"]Base[/TD]
[TD="class: xl67, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]A34763[/TD]
[TD="class: xl67"]Lid[/TD]
[TD="class: xl67, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A23767[/TD]
[TD="class: xl67"]Bolt[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A23767[/TD]
[TD="class: xl67"]Bolt[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]A84578[/TD]
[TD="class: xl67"]Nut[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]A45873[/TD]
[TD="class: xl69"]Lid 2[/TD]
[TD="class: xl69, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]A34623[/TD]
[TD="class: xl69"]Plug[/TD]
[TD="class: xl69, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 64"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sorry if my explanation was confusing before!
Thank you!
 
Upvote 0
That's happening because you have values on sheet 1 (A84578) that don't exist on sheet2 so it never moves past the bottom row on sheet1
 
Upvote 0
How about
Code: 
Sub Itzybell()
   Dim Ary As Variant
   Dim i As Long, j As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Set Dic.Item(Cl.Value) = Cl
      Next Cl
      j = 1
      For i = 2 To UBound(Ary)
         If Not Dic.Exists(Ary(i, 1)) Then
            Dic(Ary(j, 1)).Offset(1).EntireRow.Insert
            Dic(Ary(j, 1)).Offset(1).Resize(, 2).Value = Array(Ary(i, 1), Ary(i, 2))
         Else
            j = j + 1
         End If
      Next i
   End With
End Sub
 
Upvote 0
Hi Fluff! Sorry for the late reply!

I've tried your new code and it works now!! :)
Thank you so much for your help!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
217
DanteAmor
DanteAmor
S
  • Question Question
Compare several columns on two different sheets and copy the differences to another sheet
Replies
5
Views
406
sofas
S
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
M
  • Solved
Comparing two columns on different sheets and deleting the entire row if any cell is different
Replies
9
Views
349
Logit
Logit
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
280
sijpie
sijpie

Forum statistics

Threads
1,223,934
Messages
6,175,488
Members
452,648
Latest member
Candace H

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