+ Reply to Thread
Results 1 to 8 of 8

Two columns (vectors) to matrix

  1. #1
    Registered User
    Join Date
    03-22-2013
    Location
    Birmingham, AL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Two columns (vectors) to matrix

    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
    russ.elrod@ac-an.com
    Attached Files Attached Files

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Two columns (vectors) to matrix

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

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Two columns (vectors) to matrix

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

    Please Login or Register  to view this content.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  4. #4
    Registered User
    Join Date
    03-22-2013
    Location
    Birmingham, AL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Two columns (vectors) to matrix

    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
    Last edited by russ.elrod@ac-an.com; 03-25-2013 at 05:21 PM.

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Two columns (vectors) to matrix

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

  6. #6
    Registered User
    Join Date
    03-22-2013
    Location
    Birmingham, AL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Two columns (vectors) to matrix

    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
    Attached Files Attached Files

  7. #7
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Two columns (vectors) to matrix

    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

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Two columns (vectors) to matrix

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1