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 39879 articles. 2 followers. Follow

2 Replies
1771 Views

Similar Articles

[PageSpeed] 27

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:

Cannot log in using WindowsID (was able to before)
I just bought MS Money 2007 Deluxe yesterday. The install went smooth, without any issues. I registered a new WindowsID and was able to successfully log into MS Money. However, today when I tried to log into MS Money, the messge apeared that the "Money was not able to recognize your WindowsID" or something like it and that "You will still have access to some functions but will not be able to make online updates". Now I'm not able to make updates. Any idea what this could be? Is the authentication server for WindowsID down or something? See the post under Mon...

Can't use Merge feature
If I have a postcard file I've already made on the screen, when I try to pull up the "Merge" feature under Tools, it is shaded grey and I can't open it. It insists that I either form a new database list of addresses or edit the one that I want to merge. I don't want to edit the one I want to merge. I just want to MERGE it. If, however, I have just a postcard template on the screen (without it having been made into anything), I CAN pull up the "merge" feature under Tools. It is not shaded out. How do I un-shade the "merge" feature? To merg...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Restore Exchange 5.5 on 2000 using ArcServe
I've got - Windows NT 4 Server SP6 - Exchange 5.5 SP3 - ArcServeIT 6.61 with Exchange Agent I'm trying to restore the Exchange information (database, mailbox... everything) on another computer. That computer has - Windows 2000 Server SP4 - Exchange 2000 - Brighstore ArcServe 9 (build 2020) with Exchange Agent ------------------- Now the situation: I need to verify that the backup was made correctly (entirely) in order to confirm the total validity of the backup (to validate my disaster recovery plan) and to update to SP4. I tried to find documentation with both Microsoft (for Ex...

Pivot Table Question #15
Hey all, Love pivot tables but i have a question, the table is set up as so Month Pallet# BoxesPur BoxesSold Price Frt Gross Net Jan 1 5 1.0 ..3 Formula Formula 2 5 2.0 .5 3 10 3 .15 Jan Total 10 10 6.0 ..95 So, the problem is in the Jan Total row, is there a way...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Using Excel in Mac office 2004 with Microsoft/Mac 2008:Analysis toolpack
Although I am disappointed with Office 2008 for Mac because of the absent of Analysis toolpack, I figured things out quite recently that I can still install my Mac office 2004 and use the excel package that contains the Analysis tool pack. How? ...... let me show you: 1. Insert your CD for Mac office 2004 2. When it open on your desk top, two options will be given to you to download the software (a) Office set-up assistant, and (b) Microsoft office 2004. 3. CHOOSE MICROSOFT OFFICE 2004 (Drag the icon onto your desk top) and it will start to run/download. 4. When done, this will appear as ...

How to obtain enumerated attribute declarations from a DTD using MSXML & VB?
I have a VB (VB6, not .NET) application that reads an XML file (using MSXML v3.2 parser); the XML file contains a reference to an external DTD. The DTD has numerous enumerated attribute declarations like so: <!ATTLIST MyElement MyAttribute (One | Two | Three) "One" > The VB code needs to generate dynamic pick-list for all the attributes that appear in the DTD as part of a data-entry application; each dynamically created pick list should contain all the allowable values for the attribute as defined by the DTD (and presented in some kind of multiple choice control like a comb...

Using Excel spreadsheet as input to Access
Hello, I posted this in the New Users forum but only got one answer, so thought I'd try here as well. Like so many others, I am an Excel newbie. I was a mainframe COBO programmer in another life, but that was a few years back My manager would like me to write an app that will take tracking dat from an existing Excel spreadsheet (generated by our system) but onl use a select handful of columns as input to a new Access database tha I will create. I'm guessing that I can either a) create a new edited spreadsheet to b used as input to the Access database or b) use the Import wiza...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

Using Risk+ with MSP 2007
We use Risk+ as a risk simulation tool. We have discovered that it works approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run into this problem. If so, is there a remedy? My hunch this not a generic issue, but surely best that you consult with the Risk+ people on this. --rms www.rmschneider.com On 02/03/10 18:08, Tom Mc wrote: > We use Risk+ as a risk simulation tool. We have discovered that it works > approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run > into this problem. If so, is there a remedy? W...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Do money updates install when you're not admin?
I noticed when I first start using money it told me to log on as administrator so that the updates can take effect. Is this true of all money updates? When I see the message "Money update recieved" does that mean it has also been installed or do I have to log in as admin to complete the process? In microsoft.public.money, Brian H wrote: >I noticed when I first start using money it told me to log on as >administrator so that the updates can take effect. Is this true of all money >updates? When I see the message "Money update recieved" does that mean it >has a...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

how to use roundup
how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 to 1251000 , 1250750 to 1250800 thank alot =ROUNDUP(A1,-2) -- Kind regards, Niek Otten "r4319hb" <r4319hb@discussions.microsoft.com> wrote in message news:A8D5EA24-858C-49D3-A5ED-481FEBEA36CB@microsoft.com... > how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 > to > 1251000 , 1250750 to 1250800 > thank alot =CEILING(A1,100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "r4319hb" <r4319hb@discussions.microsoft...

Using 'like' on a user prompt
I want to prompt the user for a value to use to filter a form. Here is my command with the WhereCondition: docmd.OpenForm "test", acNormal, , "LastName=[Enter a Last Name]" I want to use the WhereCondition as a "LIKE" criteria. Variations on the WhereCondition itself that I've tried include various combinations of LastName LIKE %...% LastName LIKE *...* LastName LIKE [%...%] LastName LIKE [*...*] You get the idea... No luck so far. Any thoughts? On Thu, 27 May 2010 21:52:50 GMT, "RicDon" <u60397@uwe> wrote: >I wan...

Using online services from Europe
I'm going to be temporarily stationed in Europe for a couple of months. Are there any problems with my using M2002 to access my accounts, pay bills, etc, from there just as I do from the US? TIA You should be fine, so long as you have a 128 bit encryption version of IE. If you do have problems, please feel free to post here :-) -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh...

Mastering Great Plains Tables
Greetings, Does anyone have or know where I can acquire a detailed document detailing every single Great Plains table? I've used the data dictionary on and off over the years and have familiarity with a decent number of the important tables that are critical to my work, but is there something a little more detailed so that I can get at least a cursory understanding of *all* the tables? I'm thinking of creating a flash card for each table and on the back writing a brief sysnopsis of what it's used for, then regularly quizing myself. Failing anything else, I'll probably use th...

Pivot table Data field question
I create a pivot table from three columns of data, using the Wizard.In the Layout dialogue, I drag one of my fields to the Rows; I drag anther field to the Columns. I need to drag a third field to the Data area. There's no conflict there, each Data cell of the resulting matrix would be unique. Excel doesn't let me do this. It insists on using a calculation, e.g. Sum of [field3], Count of [field3], etc. How do I convince Excel to insert just the value of Field3 in the Data? -- Regards Gershon Shamay The field you drag to the data area will always be summarized. However, if the v...

Can I use the same driver in all versions of Access or Sqlserver
Hello,I have got a question,I wonder if i can use the same driver as Access2000 to get the data from all versions of Access database,my code connected to Access2000 is as follows: "m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+s.m_database,"","",adModeUnknown); Is this code available for other versions of Access? If not,can anyone tell me the correct code for Access2002.Access2003,Access97? my code connected to Sqlserver2000 is as follow: m_pConnection->Open(Provider=SQLOLEDB.1;Persist Security Info=True;Password="+s.m_pass...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...

Table SY00800 in DYNAMICS Database
Working in Microsoft Dynamics GP 9.0 Tools>>Utilities>>Purchasing>>Reconcile Process I get the messages: You can’t complete this process while transactions are being edited. I’m tried to reconcile because any time access Purchases Order Entry I get the message: Purchase Order Processing setup information is missing. Also access Batch Inquiry Inquiry>>System>>Batch show me the Purch. Order Entry Status Edit, but I don’t know how delete or change the status. Looking in SQL I found SY00800 in DYNAMICS Database that has the records all with 0 in posting’s colum...

use not use the date parameters
I have a report (and eventually more reports) that have the 2 date parameters for start date and end date. The users will want to only enter the start date and not have to enter the end date which would return all appropriate records from the start date going forward. They also want to be able to enter only the end date and get all appropriate records up to the end date. Is this possible in reporting services 2005 standard? If so, where and how? I'm still learning this ssrs stuff, but getting better at it. Thanks... John JohnE You may allow them to run the reports fr...