Add and Sort

Hello,

I have two list of number on two separate sheets. I am trying to figure out how to compare the two lists, add the numbers that are on list one that don't exist on list two to list two and finally sort them so they match. I have attached a workbook and this what I have so far:

Code:

---

Sub Addsort()
Dim rngCell As Range
Dim lastrow As Long


    For Each rngCell In Worksheets("Sheet 1").Range("A2:A10001")
        If WorksheetFunction.CountIf(Worksheets("Sheet 2").Range("A2:A100001"), rngCell) = 0 Then
            lastrow = Worksheets("Sheet 2").Cells(Rows.Count, "A").End(xlUp).Row
            Worksheets("Sheet 2").Range("A2:A" & lastrow).Value = rngCell
        End If
    Next rngCell
  Sheets("Sheet 2").Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End Sub

---

Thanks.

Hi NWE!

Code:

---

Sub test()
Dim arr, i&, d As Object
Set d = CreateObject("scripting.dictionary")
arr = Sheets(1).[a1].CurrentRegion
For i = 2 To UBound(arr)
  d(arr(i, 1)) = ""
Next i
arr = Sheets(2).[a1].CurrentRegion
For i = 2 To UBound(arr)
  If d.exists(arr(i, 1)) Then d.Remove arr(i, 1)
Next i
With Sheets(3)
  .[a2].Resize(d.Count) = Application.Transpose(d.keys)
  .[a1].CurrentRegion.Sort Key1:=.[a1], Order1:=xlAscending, Header:=xlYes
End With
End Sub

---

Hi!

It is saying that sheets 3 is out of range. I want to add the list that doesn't exist two the list on sheet 2.

So export it to column C of sheet2, something like below:

Code:

---

Sub test()
Dim arr, i&, d As Object
Set d = CreateObject("scripting.dictionary")
arr = Sheets(1).[a1].CurrentRegion
For i = 2 To UBound(arr)
  d(arr(i, 1)) = ""
Next i
arr = Sheets(2).[a1].CurrentRegion
For i = 2 To UBound(arr)
  If d.exists(arr(i, 1)) Then d.Remove arr(i, 1)
Next i
With Sheets(2)
  .[c1] = "Line"
  .[c2].Resize(d.Count) = Application.Transpose(d.keys)
  .[c1].CurrentRegion.Sort Key1:=.[c1], Order1:=xlAscending, Header:=xlYes
End With
End Sub

---

Sorry this got sent to my spam mail. I have been playing around with simply adding it to column A of sheet 2 (the ones that don't exist) and then resorting it. Using your code:

Code:

---

ith Sheets(2)
  .[A1] = "Line"
  .[A1].Resize(d.Count) = Application.Transpose(d.keys)
  .[A1].CurrentRegion.Sort Key1:=.[A1], Order1:=xlAscending, Header:=xlYes
End With


End Sub

---

Correct?

Quote:

---

Originally Posted by NWE

Hello,

I have two list of number on two separate sheets. I am trying to figure out how to compare the two lists, add the numbers that are on list one that don't exist on list two to list two and finally sort them so they match. I have attached a workbook and this what I have so far:

---

If a number is on List1 but not on List2, then add it to List2

What if there's a number on List2 that is not on List1?

No matter how you sort, they'll never match

I just need everything on list one to be on list two. If it is on list two, then it wouldn't affect list one as I wrote another function to deal with that. Would breaking it up into two subs help. For example the sort sub comes after the list adding?

I'd just do something simple like this

Code:

---

Option Explicit


Sub Addsort()
    Dim rList1 As Range, rList2 As Range, r As Range, r1 As Range
    Dim ary1() As Variant, ary2() As Variant
    Dim i As Long, n As Long
   
    With Worksheets("Sheet 1")
        Set rList1 = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With
    ary1 = Application.WorksheetFunction.Transpose(rList1)
   
    With Worksheets("Sheet 2")
        Set rList2 = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With
    ary2 = Application.WorksheetFunction.Transpose(rList2)
   
    For i = LBound(ary1) + 1 To UBound(ary1)
        n = 0
        On Error Resume Next
        n = Application.WorksheetFunction.Match(ary1(i), ary2, 0)
        On Error GoTo 0
       
        If n = 0 Then
            ReDim Preserve ary2(LBound(ary2) To UBound(ary2) + 1)
            ary2(UBound(ary2)) = ary1(i)
        End If
    Next i


    With Worksheets("Sheet 2")
        .Cells(1, 1).Resize(UBound(ary2), 1).Value = Application.WorksheetFunction.Transpose(ary2)
   
        Set r = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
        Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=r1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
   
End Sub

---

Hi,

I am going to mix it up a bit. I am going to use the insert row method. Would the argument for the range be aryl(i) from the above? Or do we need to designate another variable. I tried inserting directly into "Insert" part of function

Code:

---

  With Sheets("Sheet2").Range("A1:A10000")
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
        .Insert ary1(i) CopyOrigin:=xlFormatFromLeftOrAbove

---

Basically I am trying to not only get it to insert but to add rows as well during the insert and before the sort.