none
Convert 2 columns into multiple column table RRS feed

  • Question

  • Hi,

    I have table with two columns. See below. I need to create a kind of matrix presentation of the table, so that values in row B become column titles, see second table. Amount of data is too big to create this manually.

    TC1 Req2
    TC2 Req1
    TC3 Req0
    TC4 Req1
    TC5 Req2
    TC6 Req3
    TC7 Req4
    TC8 Req5
    TC9 Req6
    TC10 Req7
    TC11 Req8
    TC12 Req9
    TC13 Req10
    TC14 Req11
    TC15 Req12
    TC16 Req7
    TC17 Req8
    TC18 Req3
    TC19 Req13
    TC20 Req14
    TC21 Req15
    TC22 Req16

    Req1 Req2 Req3 Req4 Req5 Req6 Req7 Req8
    TC1 X
    TC2 X
    TC3 X
    TC4 X
    TC5
    TC6 X X
    TC7 X
    TC8
    TC9 X X
    TC10
    TC11 X
    TC12 X
    TC13 X X
    TC14 X X
    TC15 X
    TC16
    TC17 X
    TC18 X
    TC19
    TC20 X X x
    TC21 X
    TC22 X

    Any suggestions how to proceed are greatly appreciated!

    Question: Would Excel's PivotTable do the trick? No scripting needed?
    Friday, January 30, 2015 10:03 AM

Answers

  • That's a Crosstab Query in MS Access.  I tried a few things in Excel; I couldn't get the results you are looking for.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 1, 2015 10:19 PM

All replies

  • You could use a pivot table, but Excel would sort Req0, Req1, Req11, ..., Req16, Req2, Req3, ...
    If you could change it to Req00, Req01, ..., Req09, Req10, Req11, ..., the sorting would be OK.
    You'd have to insert a row with headers (field names) above the table.
    Create a pivot table based on the data, drag the first field to the Rows area, the second field to the Columns area, and the second field also to the Values area.

    Otherwise: let's say the data are on Sheet1, in A1:B22.
    Create a new sheet, and enter TC1 in cell A2. Fill down to A23 (or as far down as needed). Excel will automatically enter TC2, TC3, ...
    Enter Req0 in B1. Fill to the right to R1 (or as far right as needed). Excel will enter Req1, Req2, ...
    In cell B2, enter the formula

    =IF(MATCH($A2,Sheet1!$A$1:$A$22,0)=MATCH(B$1,Sheet1!$B$1:$B$22,0),"x","")

    Adjust the ranges as needed.
    Fill the formula down to the last used row, then right to the last used column.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 12:23 PM
  • Hi and thanks for your reply! I added leading zeros. I tried the formula and the result looks like this from my first sample:

    Req01 Req02 Req03 Req04 Req05 Req06 Req07 Req08 Req09 Req10 Req11 Req12 Req13 Req14 Req15 Req16
    TC1 x
    TC2 x
    TC3
    TC4
    TC5
    TC6 x
    TC7 x
    TC8 x
    TC9 x
    TC10 x
    TC11 x
    TC12 x
    TC13 x
    TC14 x
    TC15 x
    TC16
    TC17
    TC18
    TC19 x
    TC20 x
    TC21 x
    TC22 x

    So row value remain empty if column value has earlier been met. Eg. TC17. Any ideas?


    Friday, January 30, 2015 3:11 PM
  • For the formula solution, you don't need the leading zeroes (but it won't hurt either).

    Try the following array formula, confirmed with Ctrl+Shift+Enter:

    =IF(ISNUMBER(MATCH($A2&B$1,Sheet1!$A$1:$A$22&Sheet1!$B$1:$B$22,0)),"x","")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 3:33 PM
  • That's a Crosstab Query in MS Access.  I tried a few things in Excel; I couldn't get the results you are looking for.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 1, 2015 10:19 PM