Transpose and Removing Duplicates at same timeClosed

Question

Hello, 


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. 


Configuration: Windows 7 / Chrome 16.0.950.0

venkat1926 · Answer

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

jamesfly · Answer

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

Transpose and Removing Duplicates at same time

2 responses

Similar discussions

Subscribe To Our Newsletter!