Combine rows
 

Hi,

I have a data set that looks like this:


Code:
--------------------
Name City Rating Type
Dave London 5 Audi
Dave London 4 Ford
Dave London 7 BMW
Tom Dallas 6 Audi
Tom Dallas 9 Bentley
--------------------




Is it possible to combine rows so that I have only one row per name?

So I need something like this:



Code:
--------------------
Name City Audi Ford BMW Bentley
Dave London 5 4 7 -
Tom Dallas 6 - - 9
--------------------



The number of types can vary and not all names will have a particular
type. Basically I need to dynamically create columns.

Any ideas?


--
beanmonger
------------------------------------------------------------------------
beanmonger's Profile: http://www.excelforum.com/member.php...o&userid=27982
View this thread: http://www.excelforum.com/showthread...hreadid=474905

How about one row for each name/city combination. And only one rating per
name/city???

So Dave London will be one row--but Dave Dallas would be another row.

If yes, it looks like Data|pivottable would work for you.

Select your range -- include the headers
data|pivottable
follow the wizard until you get to a step that has a Layout button on it.
Click that Layout button

Drag the Name "button" to the row area
Drag the City "button" to the row Area
drag the type button to the column area
drag the rating button to the data area.
(if you see "count of", double click on that button and change it to Sum)

Now finish up that wizard.

Double click on the City "button" and change subtotal to None.

And you're done.

beanmonger wrote:

Hi,

I have a data set that looks like this:

Code:
--------------------
Name City Rating Type
Dave London 5 Audi
Dave London 4 Ford
Dave London 7 BMW
Tom Dallas 6 Audi
Tom Dallas 9 Bentley
--------------------

Is it possible to combine rows so that I have only one row per name?

So I need something like this:

Code:
--------------------
Name City Audi Ford BMW Bentley
Dave London 5 4 7 -
Tom Dallas 6 - - 9
--------------------

The number of types can vary and not all names will have a particular
type. Basically I need to dynamically create columns.

Any ideas?

--
beanmonger
------------------------------------------------------------------------
beanmonger's Profile: http://www.excelforum.com/member.php...o&userid=27982
View this thread: http://www.excelforum.com/showthread...hreadid=474905

--

Dave Peterson

Here is some code

Option Explicit

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim istart As Long
Dim ipos As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E1").Value = Range("D2").Value
Range("E2").Value = Range("C2").Value
istart = 2
For i = 3 To iLastRow
ipos = 0
On Error Resume Next
ipos = Application.Match(Cells(i, "D").Value, Rows(1), 0)
On Error GoTo 0
If ipos = 0 Then
ipos = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, ipos).Value = Cells(i, "D").Value
End If
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
istart = i
End If
Cells(istart, ipos).Value = Cells(i, "C").Value
Next i

If Not rng Is Nothing Then
rng.Delete
End If


Columns("C").Delete

End Sub



--
HTH

Bob Phillips

"beanmonger" wrote
in message ...

Hi,

I have a data set that looks like this:


Code:
--------------------
Name City Rating Type
Dave London 5 Audi
Dave London 4 Ford
Dave London 7 BMW
Tom Dallas 6 Audi
Tom Dallas 9 Bentley
--------------------




Is it possible to combine rows so that I have only one row per name?

So I need something like this:



Code:
--------------------
Name City Audi Ford BMW Bentley
Dave London 5 4 7 -
Tom Dallas 6 - - 9
--------------------



The number of types can vary and not all names will have a particular
type. Basically I need to dynamically create columns.

Any ideas?


--
beanmonger
------------------------------------------------------------------------
beanmonger's Profile:
http://www.excelforum.com/member.php...o&userid=27982
View this thread: http://www.excelforum.com/showthread...hreadid=474905

Thanks Bob.

However, the data can be more complex. Each person can have dirreftent
ratings for the same car. So


Code:
--------------------
Name City Rating Type
Dave London 5 Audi
Dave London 7 Audi
--------------------


And ideally the output should be something like


Code:
--------------------
Name City Rating Audi1 Audi2
--------------------


Is this possible?


--
beanmonger
------------------------------------------------------------------------
beanmonger's Profile: http://www.excelforum.com/member.php...o&userid=27982
View this thread: http://www.excelforum.com/showthread...hreadid=474905