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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,953
Office Version
365
Platform
Windows
@Itzybell
Not sure I understand what you are saying, can you please show an example of what is not working.
 

Some videos you may like

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

Itzybell

New Member
Joined
Jul 15, 2019
Messages
7
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,953
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,953
Office Version
365
Platform
Windows
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
 

Itzybell

New Member
Joined
Jul 15, 2019
Messages
7
Hi Fluff! Sorry for the late reply!

I've tried your new code and it works now!! :)
Thank you so much for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,953
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,859
Messages
5,489,309
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top