Re-arranging table using pivot-table?

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! 


0
5/13/2008 5:02:33 PM
excel 39880 articles. 2 followers. Follow

2 Replies
811 Views

Similar Articles

[PageSpeed] 10

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

"Orlando Acevedo" <orlando_acevedo@hotmail.com> wrote in message 
news:%23xIbksRtIHA.3968@TK2MSFTNGP04.phx.gbl...
>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!
> 


0
Bernie
5/13/2008 5:51:50 PM
With Pivot Table assist
and Index/Match:
http://www.savefile.com/files/1556310
0
5/14/2008 4:02:54 AM
Reply:

Similar Artilces:

Re-arranging table using pivot-table?
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 appreci...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

alphabetize tables
Is there a way to select a table of text in publisher and then have the program automatically alphabetize the fields. I can do this in dreamweaver, but I have not found a way to do it in publisher, and it is a handy tool to have, because a lot of times I have to enter a series of events into tables, and if I could click one button to alphabetize them, it would save me a lot of time. Thanks, ladyzylo ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button ...

Outlook rules wizard replies using From field instead of Reply-To address
This is regarding Outlook 2007 and its rules wizard. We have a mail folder with hundreds of emails that were sent on behalf of people. So the "From" and "Reply-To" are different. We created a rule so that we can reply to all of these people but the rules wizard onto uses the From filed when it processes the messages instead of the Reply-To field. This behavior only happens with the rules wizard, we can go through each email, click reply, and get the Reply-To address just fine. I understand there was an article posted by Microsoft about this issue however it was in 2003. I...

Pivot Table #2
I have a database with type and age ranges in columns. Six age ranges. One type column. The data (dollars), by type and age range are below each age range column. I want a summary report that sums the dollars by type and age combined. My preference is to have the sum of age ranges in the rows, the type in the columns with the dollar sum of each type and age range in the data fields. I hope to have the sum of the types for all ages on the right and the sum of the ages for all types at the bottom. It seems simple enough but, I don't seem to be able to make the table work? If ...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

Pivot Table Calculated Item #3
I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry H5 erry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: > I am querying an external database to retrun a pivot table, then i am adding > a calculated field that calculates Regular...

Colour Banding in pivot tables
Hi all, Is it possible to do colour banding in pivot tables? I have searched all avaible AutoFormat and I could find none, nor could I do it using conditional formatting. Thanks a lot. Frederick Chow Hong Kong. Frederick, My Excel add-in "Shade Data Rows" seems to work on Pivot tables. I just tried it out on a pivot table as have never had the need. It shades groups of like valued rows or by every nth row. Does the selection width or the entire sheet width. Choice of colors and an option to skip hidden rows. Comes with a one page Word.doc install/use file. Free upon direc...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

I need to go back to using my RR email address in RR
I switched my Road Runner email address to Outlook 2007. I no longer receive my emails at RR. How can I switch my email delivery back to RR? Call Road Runner support -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. http://www.microsoft.com/protect "jmc112449" <jmc112449@discussions.microsoft.com> wrote in message news:6FCC0D22-6056-4A2C-9BC1-135BD50E4176@microsoft.com... >I switched my Road Runner email address to Outlook 2007. I no longer receive > my emails at RR. ...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

Use Tax & the Credit Card method
I'm looking for some input on how people are managing their "Use Tax" in Great Plains with out the use of a 3rd party vendor. I've found some info on the "Credit Card" method of managing sales & Use tax (see below) and would appreciate any input on that or if anyone has other suggestions on how they are managing their "Use Tax" any info would be appreciated. Create a special "Credit Card" for the tax man. Now enter the payables > vouchers and allow the use tax due to be calculated and added to the voucher. > Before saving the...

Word table to excel
Hi Guys Is it possible to copy a word table into excel. I have a folder with circa 200 files that I need to import into Excel. I have got the code to access word but am struggling to copy the tables. Cheers I found some macro in my archieves, but not sure if I ever got them fully running. You are welcomed to try them Sub Test() Set ExSht = ActiveSheet ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next Set WDApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Set WDApp = CreateObject("Word.Application&...

Duplicating Tables in VBA
I need to create a backup of 2 tables before I perform a yearly maintenance on this database. In the current world, new tables are manually created, forms are changes, etc. I've automated everything except the copying of the tables. I've tried using the following code to create the copy of the first table but I get a message that my file cannot be found. What am I doing incorrectly? Dim strYear As String Dim strDB As String Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection strYear = Year(Date) - 1 strDB = strYear & "TeamMemberS...

Selcting data from table without repeating plus calc's
Hi Really need some help with this! I have a table where the qty are manually entered and the sections are selected from a pull down menu. Col D, F, H and J are the same pull down menues. Col B Col D Col E Col F Col G Col H Col I Col J Col K qty section length section length section length section length 5 A 2200 B 1800 B 2000 C 654 3 I 2200 B 1800 G 1587 C 254 7 A 2200 B 2100 B 2000 C 300 30 F ...

Table of Contents #3
Is there an easy way to make a table of contents. I am working on newsletters and it seems to take more time than I have to put on it. Anyone that could help me in this, please do. Hi Ruth (anonymous@discussions.microsoft.com), in the Microsoft� newsgroups you posted: || Is there an easy way to make a table of contents. I am || working on newsletters and it seems to take more time than || I have to put on it. Anyone that could help me in this, || please do. You would have to create your TOC (table of contents) in Word, or create it manually in Publisher. There are no automated TOC featur...

Linking Table Data by Form Actions
I have a form that contains record number information and alittle bit of data based on this record number. Each "record number" is linked to a couples information. This couples information is things like name, address, city, etc. When I'm on the first form, we'll call "Protocol_Index", I hand type a protocol number to designate this unique record. Once I've placed this protocol number in the "Protocol_Index" form, there's a button that takes me to another form, we'll call "Couple_Index". Once I get to the "Couple_Index...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

Using EAN barcodes
I am using SO 2.0 SP1. I need to setup some products with EAN13 barcode format. We are contemplating to use GS1 registered barcodes. I was wondering if anybody is using EAN barcodes and if there are any "gotchas" I should look out for. I would probably use a 12-digit product number, which would be good enough for our purpose. Or is there any advantage to use a 13-digit number? I also tried to print some EAN13 labels but did not find a template that prints the item code right underneath the barcode, kind of embedded in the barcode. Is that a special template that need to be us...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...

How to use Live Messenger Web Toolkit to register domain
Hello, i downloaded Live Messenger Web Toolkit sample from here : http://messenger.mslivelabs.com/Samples.zip And i want to just test it, not put it in production. I tried to register application with one of my live application domain(http://quiz.it.msn.com/), but I get error: "Domain does not exist" can you please advice how to get rid of this error....will really appreiciate any immediate reply... ...

How do you create resouces to be used
This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C4230B.97650910 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I was told that you can create resources (conference room) to be used = for scheduling. How is this done? Do you lose mailboxes? --=20 BART DANBY ------=_NextPart_000_002F_01C4230B.97650910 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META h...

re re re ????
nefertiti ??? ...

attempting to use help in Office 2007 goes to the "Configuring ...
I am trying to use the help in various Office apps, but it isn't working. When I click the help, it goes into a "Configuring Microsoft Office Ulitmate 2007..." dialog. After it runs for some period of time, the Microsoft Office Help Viewer window opens. A setup dialog then opens that says "In order to complete setup, a system reboot is necessary. Would you like to reboot now?" If I click No, another setup window pops open that says "Please wait while Windows configures Microsoft Office Word MUI (English) 2007". That disappears and then it s...