Transpose and Removing Duplicates at same time

Registration date
Saturday November 10, 2012
Last seen
January 29, 2013
 Blocked Profile -

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.

Thanks for any help.

2 replies

Registration date
Sunday June 14, 2009
Last seen
August 7, 2021
try this macro

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

in 2007, Select all rows then Home->conditional formatting(click on it)->highlight cells rules->Duplicate values(click on it)

Second Rules

in 2007, select all rows then click on Data Menu->Remove duplicates