Consolidating multiple rows of text into one

[jeff.campagnola]

I have a dataset in Excel like this:

Place Person
--------------
City1 Name1
City1 Name2
City2 Name1
City2 Name2
City2 Name3


What I need to do is manipulate the data into one row for each city,
with each name in a separate columns. Please note that there is a
varying number of names for each city. My result should be like this:

Place Person1 Person2 Person3 ... Person9
-----------------------------------------------
City1 Name1 Name2
City2 Name1 Name2 Name3

I have tried pivot tables and data consolidation but to no avail. There
are close to 2000 names, with up to 15 names in each city.
Thanks

 

[Earl Kiosterud]

Jeff,

One problem with such a solution is that you have to plan for the maximum
number of Names a given city might have. It's not considered good database
design. You might consider two tables, one for City, including any other
fields that relate to the city, then a table for Names, including any other
fields that relate to the name. The Name table would have a single field to
identify the City for that name, and then Vlookups could pull any of those
City fields you need in the Name table. It really depends on what info
you'll have, and what you need to do with it. This is the time to look
ahead.

Consider Microsoft Access for this application, using two tables, as I
mentioned. It takes care of all this kind of stuff in its sleep. You don't
need fancy formulas or anything for many applications.

 

[jeff.campagnola]

Thanks for the reply Earl. I agree that it is poor database design, but
unfortunately it is a design that I have inherited, and want to
maintain in this case because it is already intergrated into a mail
merge document. The manipulation I want to perform was done by someone
else before me, but I can't figure out how. All I know is, I need to
replicate it. Anyone have any ideas?
Jeff

 

[Dave Peterson]

How about a little macro:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim oRow As Long
Dim PrevVal As Variant

Set curWks = Worksheets("sheet1")

Set newWks = Worksheets.Add

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

newWks.Range("a1").Value = "City"
PrevVal = "SomeStringThatWon'tEverBeUsedInYourWorksheet!"
oRow = 1

For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value <> PrevVal Then
PrevVal = .Cells(iRow, "A").Value
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
oCol = 1
End If
oCol = oCol + 1
newWks.Cells(oRow, oCol).Value = .Cells(iRow, "B").Value
Next iRow
End With

With newWks
With .Range("b1", _
.Cells(1, .Cells.SpecialCells(xlCellTypeLastCell).Column))
.Formula = "=""Person #"" & column()-1"
.Value = .Value
End With
End With

End Sub

 

[jeff.campagnola]

That's great Dave - thanks! I kept my example as simple and clear as
possible, to avoid complicating the question, but in reality, my data
also has more attributes for each person (i.e. name, address, postal
code, etc...). I will try to modify the macro to accomodate this. In
total I have about almost 20 attributes per person. Again, I
acknowledge that this approach to storing my data is far from ideal, so
I thank you for understanding that and offering a solution that fits my
contsraints. I will let you know how my modification to the macro goes!
Jeff

 

[jeff.campagnola]

Got it. I added one line of code for each attribute, just before "Next
iRow", and incremented the starting column for the new worksheet based
on the maximum number of people per city (currently 9). For example:

'lastname:
newWks.Cells(oRow, oCol).Value = .Cells(iRow, "D").Value

'firstname:
newWks.Cells(oRow, oCol + 9).Value = .Cells(iRow, "F").Value

'middlename:
newWks.Cells(oRow, oCol + 18).Value = .Cells(iRow, "E").Value

'address:
newWks.Cells(oRow, oCol + 27).Value = .Cells(iRow, "C").Value

Thanks again for your help

 

[Dave Peterson]

Glad you got it working.

Got it. I added one line of code for each attribute, just before "Next
iRow", and incremented the starting column for the new worksheet based
on the maximum number of people per city (currently 9). For example:

'lastname:
newWks.Cells(oRow, oCol).Value = .Cells(iRow, "D").Value

'firstname:
newWks.Cells(oRow, oCol + 9).Value = .Cells(iRow, "F").Value

'middlename:
newWks.Cells(oRow, oCol + 18).Value = .Cells(iRow, "E").Value

'address:
newWks.Cells(oRow, oCol + 27).Value = .Cells(iRow, "C").Value

Thanks again for your help