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

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

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

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