Re-arranging table using pivot-table?

[Orlando Acevedo]

I have a worksheet with data organized somewhat like this:

New York New York
New York Albany
Florida Miami
Florida Orlando
Florida Tampa
Florida Jacksonville
California Los Angeles
California San Diego

I need to convert this to a list of states and each city in the columns to
the right of their corresponding state. Something like this:

New York New York Albany
Florida Miami Orlando Tampa
Jacksonville
California Los Angeles San Diego

Any help is greatly appreciated.

Thank you!

 

[Bernie Deitrick]

Orlando,

Use a macro. Select a cell in your table, then run the macro below. I've assumed that you have a
header row.

It will put the desired table onto a sheet named Cross Tab Data

HTH,
Bernie
MS Excel MVP


Sub DBtoCrossTab()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long

Set myTable = ActiveCell.CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab Data").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab Data"

myTable.Rows(1).EntireRow.Copy mySht.Rows(1)

Set myTable = myTable.Offset(1, 0).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("A:A"), False)) Then
myCell.EntireRow.Copy _
mySht.Range("A65536").End(xlUp)(2).EntireRow
Else
myRow = Application.Match(myCell.Value, _
mySht.Range("A:A"), False)
myCell.Offset(0, 1).Resize(1, myTable.Columns.Count - 1).Copy _
mySht.Cells(myRow, 256).End(xlToLeft)(1, 2)
End If
Next myCell

End Sub

 

[Herbert Seidenberg]

With Pivot Table assist
and Index/Match:
http://www.savefile.com/files/1556310