Previous Topic Next Topic

Transpose and Removing Duplicates at same time

Closed
calflamesfann785 Posts 2 Registration date Saturday November 10, 2012 Status Member Last seen January 29, 2013 - Jan 29, 2013 at 12:13 PM
 Blocked Profile - Jan 30, 2013 at 04:35 AM
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.


2 replies

Answer 1 / 2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 30, 2013 at 04:00 AM
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
0
Answer 2 / 2
Blocked Profile
Jan 30, 2013 at 04:35 AM
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
0