Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I combine 2 rows to make one record? | Excel Discussion (Misc queries) | |||
Combine rows clms stay the same HELP | Excel Worksheet Functions | |||
how to combine the multiple rows into one rows? | Excel Worksheet Functions | |||
How do I combine rows, combining duplicates as well as concatenati | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel |