Transpose Data

[No Idea]

Hello I am having trouble reformatting data from :-

Name Sett1 Sett2 Sett3 Sett4 Sett5 Value1 Value2 Value3 Value4 Value5
Bob 1 2 5 8 10 100 200 300 400 500
Paul 2 4 350 350
Steve 1 3 5 20 40 60

to:-

Sett Value
Bob 1 100
Bob 2 200
Bob 5 300
Bob 8 400
Bob 10 500
Paul 2 350
Paul 4 350
Steve 1 20
Steve 3 40
Steve 5 60

I have tried using a pivot table but I can't seem to get it into the format
that I'm after. I do not wish to show blanks. Can anyone help?

Thanks so much

 

[akphidelt]

Thats a fairly rough set up to be able to transpose that way... cause you
would have to manually insert rows to fit in the varied length of data per
person. The best way to do this would be through VBA, do you have an
understanding of VBA?