Combine rows

[beanmonger]

Hi,

I have a data set that looks like this:

Please Login or Register  to view this content.

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

So I need something like this:

Please Login or Register  to view this content.

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

Any ideas?

[Dave Peterson]

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

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[beanmonger]

Thanks Bob.

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

Please Login or Register  to view this content.

And ideally the output should be something like

Please Login or Register  to view this content.

Is this possible?