Transpose Question

  • Thread starter Thread starter BP
  • Start date Start date
B

BP

I have a list of data (over 6000 lines) as follows:

1 Mike 00:52.356
1 Peter 00:51.545
1 John 00:59.142
1 Joe 00:49.568
1 Allan 00:51.125
2 Mike 00:52.010
2 Peter 00:52.252
2 John 00:58.758
2 Joe 00:49.985
2 Allan 00:51.568
etc.

I want to sort the list to look as follows:

Mike Peter John Joe
Allan
1 00:52.356 00:51.545 00:59.142 00:49.568 00:51.125
2 00:52.010 00:52.010 00:58.758 00:49.985 00:51.568

etc.

I can use pivot table but the number is a time format hh:mm.sss, and when I
see the data in the pivot table it only shows it as hh:mm.000.

Essentially I just need to transpose the list, not do any suming of the
data.

Thanks.
 
Sorry the time is mm:ss.000.

When shown in a pivot table, the last three numbers are always 000 by
default and not the actual number in the list.
 
One way ..

Assume your list of data is in Sheet1,
data from row2 down
1 Mike 00:52.356
1 Peter 00:51.545
1 John 00:59.142
Click to expand...
etc

In an empty col to the right, say col D
Put in D2: =A2&TRIM(B2)
Copy D2 down

In Sheet2
-------------
Assuming you have the names listed across in B1, C1 ..
and the numbers (1, 2, etc) listed in A2 down

Put in B2:
=OFFSET(Sheet1!$C$1,MATCH($A2&TRIM(B$1),Sheet1!$D:$D,0)-1,)
Format B2 as "mm:ss.000"
Copy B2 across, then down

This should return the table you're after
 
Max,
Thank you. This almost works. My list of names is 27 names long. In Sheet 2
in cols AA, AB it does not show the correct time. Instead it jumps down a
few rows and picks up the incorrect time.
EG

0 0Chippendale Racing 00:00.000
0 0Ya Gotta Wanna 00:00.000
0 0Allied 1 00:00.000
1 1Lusty Racing 2 00:52.472
1 1Quick Connect 00:52.534
1 1Team Oz 00:52.021


Looks like:
Chippendale Racing Ya Gotta Wanna Allied 1
00:00.000 00:52.472 00:52.534


Many many thanks for the help.
 
I can use pivot table but the number is a time format hh:mm.sss, and when I
see the data in the pivot table it only shows it as hh:mm.000.

Essentially I just need to transpose the list, not do any suming of the
data.
Click to expand...

You can still use the pivot table report. And there may be an easier way to do
this, but this method works in XL2002.

Before you generate (or refresh an already existing) pivot table, in your
original data reformat the time as General. In the pivot table, you can have
the time formatted as you wish -- mm:ss.000 would be OK. It then seems to
display properly.


--ron
 
0 0Chippendale Racing 00:00.000
0 0Ya Gotta Wanna 00:00.000
0 0Allied 1 00:00.000
1 1Lusty Racing 2 00:52.472
Click to expand...

I'm cueing in that the issue probably lies
with the "numbers" part of your data in col A,
i.e. the "00", "11", etc

Try these revisions for a more robust matching:

In Sheet1
-------------
Put instead in D2:
=TEXT(A2,"000")&TRIM(B2)
Copy down as before

The above will "pad" the "numbers" part in the concatenated string

In Sheet2
-------------
Put instead in B2:

=OFFSET(Sheet1!$C$1,MATCH(TEXT($A2,"000")&TRIM(B$1),Sheet1!$D:$D,0)-1,)

Format B2 as "mm:ss.000"
Copy B2 across, then down, as before
 
Brett,

If for some reason it still doesn't work,
maybe you could email over a copy of your file
to my address given below. I'll take a look.
 
Back
Top