Answered by:
Convert 2 columns into multiple column table
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? Edited by Petri Alapiessa Friday, January 30, 2015 10:10 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.
 Marked as answer by Petri Alapiessa Monday, February 2, 2015 2:05 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)

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?
 Edited by Petri Alapiessa Friday, January 30, 2015 3:13 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)

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.
 Marked as answer by Petri Alapiessa Monday, February 2, 2015 2:05 PM