Reformatting list - a bit like a pivot but with 'real' values

[simon.harris]

I have a list of 'imported' blood test data that look like this;

Name DoB SampleID TestName TestResult
A, A 1/1/01 123456 X 1
A, A 1/1/01 123456 Y 2
A, A 1/1/01 123456 Z 3
B, B 2/2/01 123123 X 15
B, B 2/2/01 123123 Y 16
B, B 2/2/01 123123 Z 17
C, C 3/4/01 987654 X 21
C, C 3/4/01 987654 X 23
C, C 3/4/01 987654 X 25

Every sample has all of the 3 tests so what I'd like to end up with is
this;

Name DoB SampleID X Y Z
A, A 1/1/01 123456 1 2 3
B, B 2/2/01 123123 15 16 17
C, C 3/4/01 987654 21 23 25

I guess it looks a bit like a pivot table but without any calculations
(sum, count etc). What's the easiest way to acheive this? Preferably
using a 'native' function without having to mess around with macros
etc. Of course, if that's what it needs, then so be it (-;
TIA,

Simon Harris

B.t.w. We're still on Excel2000, here!

 

[Guest]

Simon:

Pivot Table is the way to go using sum and as there is only one entry the
sum will be ok.

In the layout unselect totals for rows and columns
and you will probably also have unclick on the subtotals.



Sum of TestResult TestName
Name DoB SampleID X Y Z
A,A 1/1/01 123456 1 2 3
B,B 2/2/01 123123 15 16 17
C,C 3/4/01 987654 21 23 25

 

[simon.harris]

Simon:

Pivot Table is the way to go using sum and as there is only one entry the
sum will be ok.

In the layout unselect totals for rows and columns
and you will probably also have unclick on the subtotals.

Sum of TestResult TestName
Name DoB SampleID X Y Z
A,A 1/1/01 123456 1 2 3
B,B 2/2/01 123123 15 16 17
C,C 3/4/01 987654 21 23 25

Martin,
Thanks for the speedy reply - as it happens I don't think that will do
the trick )-; What I didn't point out in the neccessarily short
example data was that some of the results are short pieces of text.
What I *really* need is a way of puttting the 'result' field into the
target table exactly as it is in the underlying data.
Any other ideas out there?

Simon

 

[Herbert Seidenberg]

Proceed as Martin explained, then translate the results with lookup.
Assume your data looks like this:

Name DoB SampleID TName TResult TCode
A,A 1/1/2001 123456 X Icg 1
A,A 1/1/2001 123456 Y Kce 2
A,A 1/1/2001 123456 Z Niv 3
B,B 2/2/2001 123123 X Atr 4
B,B 2/2/2001 123123 Y Lsf 5
B,B 2/2/2001 123123 Z Hos 6
C,C 3/4/2001 987654 X Dla 7
C,C 3/4/2001 987654 Y Ehu 8
C,C 3/4/2001 987654 Z Cqs 9

Add a helper column, TCode, of unique, sorted numbers.
Use TCode instead of TResults in your pivot table.
The PT might look like this:

Name DoB SampleID X Y Z X' Y' Z'
A,A 1/1/2001 123456 1 2 3 Icg Kce Niv
B,B 2/2/2001 123123 4 5 6 Atr Lsf Hos
C,C 3/4/2001 987654 7 8 9 Dla Ehu Cqs

Generate the X' Y' Z' table with this array formula:
=LOOKUP(XYZ_array,TCode,TResult)

 

[simon.harris]

Martin,
Thanks for the speedy reply - as it happens I don't think that will do
the trick )-; What I didn't point out in the neccessarily short
example data was that some of the results are short pieces of text.
What I *really* need is a way of puttting the 'result' field into the
target table exactly as it is in the underlying data.
Any other ideas out there?

Simon- Hide quoted text -

- Show quoted text -

Cracked it (-; Just in case anyone else is interested. Pivot tables
ARE the way to go but I couldn't use 'sum' because some of the entries
weren't numbers but text. So instead I used 'first'! As there was only
one entry for each cell, it just put the value into the pivot table.
Thanks,

Simon Harris