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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Simplest way is to sort sheet1 after updating it.
 
Upvote 0
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
 
Upvote 0
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:
[TABLE="width: 178"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]No.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]A34732[/TD]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="width: 172"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]No.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]A34763[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]A34732[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]A84578[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD]A45734[/TD]
[TD]Rose[/TD]
[/TR]
</tbody>[/TABLE]

OUTPUT (Sheet1):

[TABLE="width: 183"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]No.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]A10256[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]A23767[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]A34732[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]A34763[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]A84578[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD]A45734[/TD]
[TD]Rose[/TD]
[/TR]
</tbody>[/TABLE]

------------------------------
Desired output (Sheet 1):
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]No.[/TD]
[TD="class: xl66, width: 64"]Name[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A10256[/TD]
[TD="class: xl65, width: 64"]Jane[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]A34763[/TD]
[TD="class: xl67, width: 64"]Tom[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A23767[/TD]
[TD="class: xl65, width: 64"]Mary[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]A34732[/TD]
[TD="class: xl65, width: 64"]John[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]A84578[/TD]
[TD="class: xl67, width: 64"]Jerry[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]A45734[/TD]
[TD="class: xl67, width: 64"]Rose[/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0
Do you just want to copy over cols A & B, or are there other columns that need to be copied?
 
Upvote 0
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
 
Upvote 0
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:

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Sheet 1:[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] No.
[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A10256[/TD]
[TD="width: 64, bgcolor: transparent"]Jane[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A23767[/TD]
[TD="width: 64, bgcolor: transparent"]Mary[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A34732[/TD]
[TD="width: 64, bgcolor: transparent"]John [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Sheet 2:[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] No.
[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A10256[/TD]
[TD="width: 64, bgcolor: transparent"]Jane[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A34763[/TD]
[TD="width: 64, bgcolor: transparent"]Tom[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A23767[/TD]
[TD="width: 64, bgcolor: transparent"]Mary [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]OUTPUT (Sheet1):[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] No.
[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A10256[/TD]
[TD="width: 64, bgcolor: transparent"]Jane[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A34763[/TD]
[TD="width: 64, bgcolor: transparent"]Tom[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A23767[/TD]
[TD="width: 64, bgcolor: transparent"]Mary[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]A34732[/TD]
[TD="width: 64, bgcolor: transparent"]John [/TD]
[/TR]
</tbody>[/TABLE]
------------------------
When comparing Column A, once a data is different it will automatically be inserted into Sheet1 to the row.

Thank you! :)
 
Upvote 0
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!
 
Upvote 0
Hi I have similar problem


[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]X[/TD]
[TD="class: xl66, width: 64"]Y[/TD]
[TD="class: xl66, width: 64"]URL[/TD]
[TD="class: xl66, width: 64"]A [/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]Solution[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl67"]www.1.com[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl67"]www.2.com[/TD]
[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl67"]www.1.com[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl67"]www.3.com[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl67"]www.4.com[/TD]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl66"]



[/TD]
[/TR]
</tbody>[/TABLE]



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
 
Upvote 0
@jpmayekar
Please start a thread of your own, rather than "hijacking" somebodyelse's thread.
Thanks
 
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,933
Messages
6,175,475
Members
452,646
Latest member
tudou

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