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
1781 Views

Similar Articles

[PageSpeed] 32

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:

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Microsoft CRM using Firefox
Hello, Would anyone by chance know if CRM will work correctly on Firefox. Currently, I have employees that are using Firefox and are encountering the same issue as IE [Internet Explorer bombs out and states that it has encountered problems...]. I am just having employees use IE to prevent problem. Thank you for your comments in advance. Install a Firefox extension called IETab "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:19B93E29-757D-46FF-B747-A17EB112ED94@microsoft.com... > Hello, > > Would anyone by chance know if CRM will work cor...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Is anybody using Symantec Enterprise Vault?
Hi, A vendor has recommended this solution to us, I have looked at the datasheet for the product and it speaks the right language but I was wondering if anybody on here uses it in a live environment and what their opinion of it is. Cheers Sean ...

unable to access mail after re-enable account
Hi, After enable an disable account and change its password, it can login to its mail account. What's happening. Is it normal? Thanks a lot What is the error message when user tries to access mailbox? Does the user still show in ADUC has having Exchange attributes? Have you looked at the MB using ESM to see if it is disconnected? "Raymond" <anonymous@discussions.microsoft.com> wrote in message news:136c01c46f11$d16f8390$a301280a@phx.gbl... > Hi, > > After enable an disable account and change its password, > it can login to its mail account. What's hap...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Operating a small passenger service
I am hoping someone has managed to customise outlook in a way that allows you to use the programe to manage bookings on a small passenger service. I.e input a new booking, number of people, location etc. Then email the days bookings to the different drivers. Maybe it could also be set up to know how many seats are avialale to reduce the risk of double booking? Im using Outlook 2003. ...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

trouble using signature picker
The signature picker is ghosted out does anyone know how to correct this ? Signature picker was working earlier. Thanks ...

XP Look and Feel in Windows 2000 using ComCtrl32.dll
Hai I Want to have a XP Look and feel under windows2000 using VC++ and ComCtrl32.dll and does any one have any idea please help me as basically iam looking to develop a Skin Waiting for your reply suresh You will have to draw many of your own components. You could purchase a library like the one from www.codejock.com or www.dundas.com or or www.bcgsoft.com use something like this: These are for Office XP, but you'll get the idea. It's a ton of work: http://www.codeproject.com/miscctrl/officexp.asp Tom "suresh" <s...

Outlook 2007: is there a way to use it as a Diary
I'd like to keep a daily journal of important notes, etc. In other words , I would like to keep a Diary. Does Outlook 2007 have this capability? TIA Sure - Ctrl+8 to view the journal or you can create a Mail and Post folder type and post to it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

VBA using an image as a toggle switch
Hi hope you can help. I know next to nothing about VBA and have adapted code below from stuff I had. I simply want to show hide portions of my page to cut down on screen clutter/information overload. I have assigned my macro to a screen image but was wondering if I could have a hide and unhide under the same button. Any help would be much appreciated Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = True Rows("98:148").EntireRow.Hidden = True Rows("171:221").EntireRow.Hidden = True Rows("244:294").EntireRow.Hidden = T...

Read file info using SMO locks database!
Hi! I'm using SMO from C# to restore a database from a backup file. Since I want to re-use the same datafiles I try to read the datafiles in my destination database and call RelocateFile to use these filenames during the restore. Everyting works fine except when I try to read the current datafilenames (see code below). If I skip this code and just hardcode the paths in relocatefile everything works fine. Database db = svr.Databases[destinationDBName]; foreach (FileGroup group in db.FileGroups) foreach (DataFile file in group.Files) Console.WriteLine...

OWA using mix of machine name/FQDN, screwing things up with HTTPS
Hello, We have one particular customer whose SBS 2003 box is giving me some heartburn when it comes to setting up OWA. I've set it up for HTTPS only, and setup permissions the exact same as our other many working OWA systems. Here's what happens when I try and hit the "broken" one: When I go to: https://server.domain.com/exchange, I get prompted for a username and password, which works properly. After login, I get a warning that the page contains a mix of both secure and non secure items. If I don't display the non secure items, I see the two "panes"...

"Arranged" files scroll together
This is driving me crazy... I often display several files at once so that I can see different sections. Ever since I've had Excel 2003, all the "arranged" files scroll together when I move in one of them. This makes it impossible to see the areas that I need. I've tried both the Arrange and the Compare Side by Side (which, by the way, is a very frustrating new utility), but cannot figure out how to keep the non-active files from being affected by cursor movements. I waste literally hours a day trying to deal with this. ...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...

Using a Tool to Modify Email Downloads (GGI)
Hello, If all possible, I would like to see Exchange pop email sooner than 15 minutes which appears to be the minimum it allows. Is there a Microsoft tool that allows to modify the download feature sooner than 15 minutes for email on the server? If yes, can someone please shed some light... Thank you in advance! POP is a email client access protocol. Exchange Server is not an email client. http://www.swinc.com/resources/exchange/smtp_diatribe.asp "George G. Ioannides" <gioannides@softhome.net> wrote in message news:%23iHAY2B3FHA.3868@TK2MSFTNGP12.phx.gbl... > &...

Common uses for XML in an asp web application
I recently created an application that used an xml file to define 4 different groups used for my app, and within each of those groups are <item></item> tags to declare different values that were used to lookup values in a database. This worked pretty well to allow me to dynamically create navigation buttons to perform operations etc.. Now i am trying to think of other common uses for xml that would make my apps even more scalable. I think I could have used xml again in my app for this operation althought i did not because of time constraints: Once records were grabbed from the d...