Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Compare two columns of two sheets and insert row if new data found
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    @Itzybell
    Not sure I understand what you are saying, can you please show an example of what is not working.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 Id Description Level
    A10256 Base 1
    A34763 Lid 2
    A23767 Bolt 3
    A23767 Bolt 3
    A84578 Nut 4


    Sheet 2
    Item Id Description Level
    A10256 Base 1
    A45873 Lid 2 2
    A34623 Plug 2
    A23767 Bolt 3

    Output:
    Item Id Description Level
    A10256 Base 1
    A45873 Lid 2 2
    A34623 Plug 2
    A34763 Lid 2
    A23767 Bolt 3
    A23767 Bolt 3
    A84578 Nut 4

    However, when I try it on my actual BOM file,
    the output seems to become like this:
    Item Id Description Level
    A10256 Base 1
    A34763 Lid 2
    A23767 Bolt 3
    A23767 Bolt 3
    A84578 Nut 4
    A45873 Lid 2 2
    A34623 Plug 2

    Sorry if my explanation was confusing before!
    Thank you!

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Fluff! Sorry for the late reply!

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

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •