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

    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


    and need it to look as the following crosstab

    (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
    Houston, TX
    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
    It's AWL Good!

  3. #3
    Join Date
    Jan 2008
    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
    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