Sorting 2 columns to match when slightly different

[Keegan4123]

Have a daily data set I need to combine.
1st set contains all vehicles, 2nd set contains only vehicles used today, so far have been pasting them next to eachother, then manually sorting line by line so that the same units line up, and we can perform the calculations. Is there a way to easily sort the two ranges so that they match line by line. One would either have gaps (thats okay) or one would be out of sort (have top sorted, and unused units on the bottom (also okay)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Intial Data Set
2	GPS System				PRETRIP SYSTEM
3	Unit Number	KM			Unit Number	KM
4	Unit 1	85,000			Unit 1	85000
5	Unit 2	86,000			Unit 3	87000
6	Unit 3	87,000			Unit 4	88000
7	Unit 4	88,000			Unit 6	90000
8	Unit 5	89,000			Unit 7	91000
9	Unit 6	90,000			Unit 9	93000
10	Unit 7	91,000
11	Unit 8	92,000
12	Unit 9	93,000
13
14
15	Option 1							Option 2
16
17	GPS System				PRETRIP SYSTEM			GPS System				PRETRIP SYSTEM
18	Unit Number	KM			Unit Number	KM		Unit Number	KM			Unit Number	KM
19	Unit 1	85,000			Unit 1	85000		Unit 1	85,000			Unit 1	85000
20	Unit 3	87,000			Unit 3	87000		Unit 2	86,000
21	Unit 4	88,000			Unit 4	88000		Unit 3	87,000			Unit 3	87000
22	Unit 6	90,000			Unit 6	90000		Unit 4	88,000			Unit 4	88000
23	Unit 7	91,000			Unit 7	91000		Unit 5	89,000
24	Unit 9	93,000			Unit 9	93000		Unit 6	90,000			Unit 6	90000
25	Unit 2	86,000						Unit 7	91,000			Unit 7	91000
26	Unit 5	89,000						Unit 8	92,000
27	Unit 9	93,000						Unit 9	93,000			Unit 9	93000
28
Sheet1

 

[DanteAmor]

Hi @Keegan4123, thanks for posting on the forum.


Try this macro to get option 2. The macro assumes, as your example does, that the data starts in cell A4 and you want the result starting in cell E4 onwards.

Sub SortingColumns()
  Dim a As Variant, b As Variant, c As Variant
  Dim rng As Range, f As Range
  Dim i As Long, j As Long, k As Long
  
  Set rng = Range("A4", Range("A" & Rows.Count).End(3))
  a = Range("E4:F" & Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To rng.Rows.Count, 1 To 2)
  ReDim c(1 To rng.Rows.Count, 1 To 2)
  
  For i = 1 To UBound(a, 1)
    Set f = rng.Find(a(i, 1), , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      j = f.Row - 3
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
    Else
      k = k + 1
      c(k, 1) = a(i, 1)
      c(k, 2) = a(i, 2)
    End If
  Next
  
  Range("E4:F" & Rows.Count).ClearContents
  Range("E4").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Range("E" & Rows.Count).End(3)(2).Resize(UBound(c, 1), UBound(c, 2)).Value = c
  
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Keegan4123]

Hi @Keegan4123, thanks for posting on the forum.


Try this macro to get option 2. The macro assumes, as your example does, that the data starts in cell A4 and you want the result starting in cell E4 onwards.

Sub SortingColumns()
  Dim a As Variant, b As Variant, c As Variant
  Dim rng As Range, f As Range
  Dim i As Long, j As Long, k As Long
 
  Set rng = Range("A4", Range("A" & Rows.Count).End(3))
  a = Range("E4:F" & Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To rng.Rows.Count, 1 To 2)
  ReDim c(1 To rng.Rows.Count, 1 To 2)
 
  For i = 1 To UBound(a, 1)
    Set f = rng.Find(a(i, 1), , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      j = f.Row - 3
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
    Else
      k = k + 1
      c(k, 1) = a(i, 1)
      c(k, 2) = a(i, 2)
    End If
  Next
 
  Range("E4:F" & Rows.Count).ClearContents
  Range("E4").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Range("E" & Rows.Count).End(3)(2).Resize(UBound(c, 1), UBound(c, 2)).Value = c
 
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thanks so much for your suggestion! That looks like it will work perfectly from my limited knowledge on macros, but I was able to find a bit more of an easy solution using a helper column and the formula =match(a4,E:E,false) then sorting the range by the result.