Creating a list of value from an array

M

Moanster

Hi all,
I know I've gotten this answer before by searching the forum, but I cannot
find it now. I have an array of values

userid value1 value2 value3
u001 58 64 99
u002 104 13 56
u003 23 15 86

and I want to create a list showing each distinct value for the user, like so:

u001 58
u001 64
u001 99
u002 104
u002 13
u002 56
u003 23
u003 15
u003 86

As I recall, this could be done by a pvot table, doing something strange
with the pivot. Does anyone have any suggestions?

Thanks
 
T

Teethless mama

Assume your data A1:D4 with the header in Row 1
A2: u001, B2: 58, C2: 64, D2: 99
A3: u002, B3: 104 and so on....

Formulas:

In A7: ="u"&TEXT(INT((ROWS($1:1)-1)/3)+1,"000")
copy down

In B7: =INDEX($B$2:$D$4,ROWS($1:3)/3,MOD(ROWS($1:1)-1,3)+1)
copy down
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top