Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Unanswered: List to Crosstab

    Hi guys,
    I've been eating my brain with this and need some help
    I have a list that looks as following

    Chicago__NY_________200
    Chicago__Sanfrancisco_300
    miami____chicago_____400
    miami____sanfrancisco_100

    and need it to look as the following crosstab
    ________chicago___ny____sanfrancisco__miami
    chicago____0______200______300_______-
    ny_________-_______0________-________-
    miami______400______-_______100______0

    (i included the underscores to keep the table in shape as the editor eliminates the extra spaces)

    where the same with same equals 0, if the data is not in the list then "-".

    Can anyone help???? i'll buy loads of beers!
    cheers, C

  2. #2
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    A pivot table will do what you are wanting.
    Put column A into the rows
    Put column B into the columns
    put Column C into the data
    texasalynn
    It's AWL Good!

  3. #3
    Join Date
    Jan 2008
    Posts
    19
    texas is almost right, but:

    - the rows will only contain Chicago & miami, since column A only contains these two names
    - The columns will only contian NY, Sanfransisco and chicago, same reason as the first note.
    - Combining Chicago with Chicago will give an empty cell, not 0
    - Any combinations that have no information would also give an empty cell
    - And because of points 1 and 2, you will not get a "-" when there is no data present for some of the names (since they are not represented in the rows or columns)

    So that puts you back to the drawing board... One important question:

    Why is the data ordered as you show, can you change its format or are you stuck with it?

    I am afraid that if you are stuck with this format, you are forced to create something in VBA (unless someone else has a better suggestion), but that should not be very hard

  4. #4
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    I agree with texasalynn,

    But what you need to do then is right click on the created pivot table, and select “table options”, in the options window on the right hand side “for empty cells” enter a zero, and your blank cells within the pivot table show zero value not null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •