Thread: Convert Data from Columns to Rows

Hi All,

I'm trying to avoid copying and pasting the data one cell at a time.

What I have are rows of test results. The data is
Column A = date (mm/dd/yy - formated as mm/yy) The day doesn't matter
Column B = host name
Column c = recovery time (format is hh:mm:ss - This time represents the difference between the start and end time)

Sample of data
08/01/06 abc 22:15:00
08/01/06 def 26:14:10
11/01/06 abc 15:10:45
11/01/06 def 31:14:12
11/01/06 ghi 22:34:40
11/01/06 jkl unrecoverable
03/01/07 abc unrecoverable
03/01/07 def 18:12:45
08/01/07 abc 29:45:13

Currently I have about eight different test dates of data. A host may exist in all eight test dates or only a couple.

What I want to end up with is a chart like this.

Host 08/01/06 11/01/06 03/01/07 08/01/07
abc 22:15:00 15:10:45 unrecoverable 29:45:13
def 26:14:10 31:14:13 18:12:45
ghi 22:34:40
jkl unrecoverable

Any help would be appreciated.

se1429,

Thanks for the suggestion, but I keep getting range doesn't match. I believe
the problem with Transpose is that the rows and columns aren't equal. One row may have 5 columns and the next row may have only 3 columns.

I'm looking for a formula that will match on the host name and month in the date and then set the cell in the new worksheet equal to the value in the matched data. This way when I get new data, I can just paste the new data into my data worksheet and the formulas will copy the data to the second worksheet.

I found the answer in another thread. It is a combination of INDEX and MATCH functions.

Sorry, I forgot.


I used this formula

=INDEX(Data!$C$2:$C$1000,MATCH(1,(Data!$B$2:$B$1000=$A3)*(INT(Data!$A$2:$A$1000)=$F$2),0))