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

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


"Moanster" wrote:

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

Thanks. I think there was a more automatic way, but cannot find it.

"Teethless mama" wrote:

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


"Moanster" wrote:

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