Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

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

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

    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
    A100 Obj1
    A300 Obj3
    A400 Obj4
    Sheet 2:
    No. Name
    A100 Obj1
    A200 Obj2
    A300 Obj3
    A400 Obj4
    A500 Obj5
    A600 Obj6

    OUTPUT(Sheet 1):
    No. Name
    A100 Obj1
    A300 Obj3
    A400 Obj4
    A200 Obj2
    A500 Obj5
    A600 Obj6
    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
    A100 Obj1
    A200 Obj2
    A300 Obj3
    A400 Obj4
    A500 Obj5
    A600 Obj6

    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.

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

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

    Simplest way is to sort sheet1 after updating it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    Try:
    Code:
    Sub CompareCols()
        Application.ScreenUpdating = False
        Dim ws1 As Worksheet, ws2 As Worksheet, i As Long, v1 As Variant, v2 As Variant, LastRow As Long
        Set ws1 = Sheets("Sheet1")
        Set ws2 = Sheets("Sheet2")
        v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Value
        v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
        Application.ScreenUpdating = False
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(v1, 1)
                If Not .Exists(v1(i, 1)) Then
                    .Add v1(i, 1), Nothing
                End If
            Next i
            For i = 1 To UBound(v2, 1)
                If Not .Exists(v2(i, 1)) Then
                    ws1.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = v2(i, 1)
                    ws1.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = v2(i, 2)
                End If
            Next i
        End With
        LastRow = ws1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With ws1.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:B" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    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!

    I may have to rephrase my question,
    My Col A is actually Id number that is not of an ascending order,
    and col B is actually a description:

    Sheet 1:
    No. Name
    A10256 Jane
    A23767 Mary
    A34732 John

    Sheet 2:
    No. Name
    A10256 Jane
    A34763 Tom
    A23767 Mary
    A34732 John
    A84578 Jerry
    A45734 Rose

    OUTPUT (Sheet1):

    No. Name
    A10256 Jane
    A23767 Mary
    A34732 John
    A34763 Tom
    A84578 Jerry
    A45734 Rose

    ------------------------------
    Desired output (Sheet 1):
    No. Name
    A10256 Jane
    A34763 Tom
    A23767 Mary
    A34732 John
    A84578 Jerry
    A45734 Rose


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

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

    Do you just want to copy over cols A & B, or are there other columns that need to be copied?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

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

    If it is just the 1st 2 columns, try
    Code:
    Sub Itzybell()
       Dim Ary As Variant
       Dim i As Long, j As Long
       
       Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
       With Sheets("Sheet1")
          j = .Range("A" & Rows.Count).End(xlUp).Row
          For i = UBound(Ary) To 2 Step -1
             If .Cells(j, 1).Value = Ary(i, 1) Then
                j = j - 1
             Else
                Rows(j + 1).Insert
                .Cells(j + 1, 1).Resize(, 2).Value = Array(Ary(i, 1), Ary(i, 2))
             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

  7. #7
    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 Mumps!

    I realized that your macro would sort and group the duplicates together.

    The macro I would need have to insert new rows from Sheet 2 to Sheet 1 without messing up the data if it is the same data in Col A (Sheet 1 and 2)

    E.g:

    Sheet 1:
    No. Name
    A10256 Jane
    A23767 Mary
    A34732 John

    Sheet 2:
    No. Name
    A10256 Jane
    A34763 Tom
    A23767 Mary

    OUTPUT (Sheet1):
    No. Name
    A10256 Jane
    A34763 Tom
    A23767 Mary
    A34732 John
    ------------------------
    When comparing Column A, once a data is different it will automatically be inserted into Sheet1 to the row.

    Thank you!

  8. #8
    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,

    The code worked for a sample data set that had small amount of edited rows,
    However when I import it into my data file which had about 50+ new data sets in Sheet 2 it wasn't able to insert into the respective places in Sheet 1 and the new data ends up at the bottom of data of Sheet 1 again.

    Is there a condition that I had to follow?..

    On the question if other columns are to be copied,I would actually need to map over the data from Column J to S as well.
    But the unique identifier would still be Column A.

    Thank you!

  9. #9
    New Member
    Join Date
    Oct 2017
    Posts
    8
    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 I have similar problem


    X Y URL A B Solution
    1 2 www.1.com 2 3
    2 3 www.2.com 1 2 www.1.com
    3 4 www.3.com 4 5
    4 5 www.4.com 3 4




    If Column A and B Matches with Column X and Y then URL Next to X and Y should be inserted in Solutions. one example is shown . can you help

    Thanks well in advance

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

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

    @jpmayekar
    Please start a thread of your own, rather than "hijacking" somebodyelse's thread.
    Thanks
    - 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
  •