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!
- 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
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