Two columns (vectors) to matrix

**[email protected]** · 03-25-2013, 03:13 PM

How can I convert two columns of information into a matrix? I've attached my spreadsheet for your review.
Basically, I have a list of lanes traveled by our trucks, i.e. AL to GA, AL to CA, etc, etc. These are in two columns. I would like see the "count" of how many times we ran each lane, such as "AL to CA", etc, with the results populated in a state-to-state matrix.

Thanks in advance for your help on this.

Russ
[email protected]

**judgeh59** · 03-25-2013, 03:26 PM

If I understand what you are looking for a pivot table may be the answer....please attached....HTH

**icestationzbra** · 03-25-2013, 03:33 PM

put this in cell E7, then drag-fill right and down:

Please Login or Register  to view this content.

**[email protected]** · 03-25-2013, 05:08 PM

JudgeH59,

If you read this reply prior to my edit, please disregard. I figured out the pivot table. Thanks so much!

icestationzbra, Your function worked perfect as well. Thanks very much also.

Great stuff you guys!

Russ

**judgeh59** · 03-25-2013, 05:14 PM

if you click in the pivot table, two new tabs will appear above the ribbon....one is called Options....click on the that and toward the right you'll see field list....click on field list it will show you how I created the pivot table....HTH....

**[email protected]** · 03-26-2013, 12:55 PM

Couple of quick questions. I've attached the updated Excel file / pivot table for your review...

(1) The resulting pivot table is a state-to-state matrix. However, it only lists the states which had activity (...obviously). How can I list all 48 state codes as the row and column labels?

(2) Is there a way (formula) for blank cells show "0" (zero). I'm using a conditional formatting "heat map" and it would nice if the blank cells showed "0".

Thanks
Russ

**judgeh59** · 03-26-2013, 01:33 PM

to get zeros in blank cells right click on any cell in the PT. Open Pivot Table options....in the Layout & Format Tab (1st tab)...there is a format section....put a check in the "For Empty Cells Show:" and the put an 0 in the box next to that.....that should fix question 2....still working on question 1

**judgeh59** · 03-26-2013, 02:02 PM

I'm not coming up with a clean way of adding all 50 states....I did try adding a helper column in the data WS called All States and then added every state 2 character initials....in the next column did a VLOOKUP looking to see if that state is in the list....then find the good states and autofilter those out of the All state list.....copy and paste the list of false states to the bottom of your Shipper state and Consignee state....then you have to change the Data source in your PT because you just added some rows....then adjust your conditional formatting to include the new PT cells....that's about as clean as I can get it....not sure this help much....