I have a spreadsheet with two columns. The left column is an eight digit house ID. The right column represents miles driven by individual vehicles. Due to many households having multiple vehicles, there are going to be houseID that appear more than once.
The problem is that I want only one house ID to appear in the left. Additionally, I want the miles driven numbers to appear horizontally, hence a transpose. Below is an example.
What is currently in place:
HouseID Miles Driven
12341234 35
12341234 35
12351234 45
12351234 46
What I want it to look like:
12341234 35 35
12351234 45 46
The second and third columns would represent the miles driven for each vehicle. The vehicle is not identified by any other means other than number of miles driven.
This may not be possible but I would be eternally grateful. I have been doing this manually and it takes forever.
Sub test()
Dim id As Range, cid As Range, result As Range
Dim idfind As Range
Dim j As Integer, k As Integer
Dim miles As Long
Range("a1").CurrentRegion.Sort key1:=Range("A1"), Header:=xlYes
Set id = Range(Range("A1"), Range("a1").End(xlDown))
Set result = Range("A1").End(xlDown).Offset(5, 0)
MsgBox result.Address
id.AdvancedFilter xlFilterCopy, , result, True
Set result = Range(result.Offset(1, 0), result.End(xlDown))
For Each cid In result
k = WorksheetFunction.CountIf(id, cid)
Set idfind = id.Find(what:=cid, lookat:=xlWhole)
For j = 1 To k
miles = idfind.Offset(j - 1, 0).Offset(0, 1)
Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = miles
Next j
Next cid
End Sub
