Transpose data - more complex

[brendanolear]

Hi seeking guidance on the following:

I have a table of data with staff names in Column A and Performance numbers in same row thereafter.

What I am trying to do is convert the top table in my example to the bottom table but do not know how to; using either transpose data or vlookup/hlookup etc.

I can do it with lots of cutting and pasting but there must be a simple way - please.

Name	P1	P2	P3	P4
A.Smith	50	60	42	35
C.Jones	25	12	16	18
N.Brown	45	12	16	14
A.Smith	P1	50
A.Smith	P2	60
A.Smith	P3	42
A.Smith	P4	35
C.Jones	P1	25
C.Jones	P2	12
C.Jones	P3	16
C.Jones	P4	18
N.Brown	P1	45
N.Brown	P2	12
N.Brown	P3	16
N.Brown	P4	14

<colgroup><col width="64" style="width:48pt" span="5"> </colgroup><tbody>
</tbody>

 

[MickG]

If you can use a bit of code, this will place the desired result on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG19Sep04
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nRay(1 To UBound(Ray, 1) * UBound(Ray, 2) + 1, 1 To 3)
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
        c = c + 1
        nRay(c, 1) = Ray(n, 1)
            nRay(c, 2) = Ray(1, Ac)
                nRay(c, 3) = Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A2").Resize(c, 3)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brendanolear]

Superb MickG, that is an excellent solution to my problem. thank you.

 

[MickG]

You're welcome