Excel 02 question - making a column into a row

[JoseQuiervo]

I currently was given an Excel project for test data reporting. There are groupings by TestName, TestType then columns by TestData, TestTime, and TestNumber. Now, when I pull them out of MS Access into Excel they come in as columns only. What I need is for the TestNumber to pull in as a row that goes from TestNumber 1-100 and the TestData dropping below each test number. Now, there could be 50 cells of TestData for any given TestNumber. So setting them both up as rows won't work. For example

TestNumber 1 2 3 4 5
Test Data .003 .002 .002 .012 .056
.053 .064 .089 .001 .004

If anyone has any ideas or tips for this, I appreciate it immensely. Thank you.

 

[lupins46]

Sounds like you need to be creating a pivot table in Excel.
(I am guessing that the data is held as individual test records in Access so you have to do something to transform the rows into columns.)

 

[JoseQuiervo]

Yes, the test data is in a single table in an Access database. So, a pivot table should work? I'll look into. Thanks, a little direction was all I needed.

 

[JoseQuiervo]

Pivot table uses totals, I have no desire for any totals...it does help to display the one piece correctly but the rest isn't what I'm looking for. Any other ideas?

 

[mikej336]

If I understand your problem correctly. There will be many "test data" for a given number of "test numbers".

From your example your only worried about corralating the two.

I would sort the Access table by "test number". Then write a program that reads the table and copies the "test data" down a particular column as long as the "test number" stays the same. when it changes go back to the top and start over again in the next column.

If I am missing something let me know.

Uncle Mike

 

[mscallisto]

If you highlite your columns (e.g. A1...E1) then Edit > copy

Place your cursor in B1 and Edit paste-special and click transpose > ok

Is this what you want?

 

[JoseQuiervo]

Well, currently, TestNumber goes side-by-side with TestData (ie)

1 .001
1 .004
1 .056
1 .049

So, transposing it won't group the test numbers to only display one of each for all the TestData that corresponds to it. This may not be all that possible for what they want. Just checking to see if anyone had any ideas.

 

[mikej336]

Even quicker, just query the table for a given "test number" and copy the record set to a given column. Pseudo code below.

for x=1 to 100
Select [test data] from whatever where [test number]=x
copy recordset to column x
x=x+1
next x

Uncle Mike