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

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:
No.Name
A100Obj1
A300Obj3
A400Obj4

<tbody>
</tbody>
Sheet 2:
No.Name
A100Obj1
A200Obj2
A300Obj3
A400Obj4
A500Obj5
A600Obj6

<tbody>
</tbody>

OUTPUT(Sheet 1):
No.Name
A100Obj1
A300Obj3
A400Obj4
A200Obj2
A500 Obj5
A600Obj6

<tbody>
</tbody>
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:
No.Name
A100Obj1
A200Obj2
A300Obj3
A400Obj4
A500 Obj5
A600Obj6

<tbody>
</tbody>

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.
 
@Itzybell
Not sure I understand what you are saying, can you please show an example of what is not working.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:
Item IdDescriptionLevel
A10256Base1
A34763Lid2
A23767Bolt3
A23767Bolt3
A84578Nut4

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

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


Sheet 2
Item IdDescriptionLevel
A10256Base1
A45873Lid 22
A34623Plug2
A23767Bolt3

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

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Output:
Item IdDescriptionLevel
A10256Base1
A45873Lid 22
A34623Plug2
A34763Lid2
A23767Bolt3
A23767Bolt3
A84578Nut4

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

However, when I try it on my actual BOM file,
the output seems to become like this:
Item IdDescriptionLevel
A10256Base1
A34763Lid2
A23767Bolt3
A23767Bolt3
A84578Nut4
A45873Lid 22
A34623Plug2

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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