Horizontail to Vertical

[slamanager]

Hi,

Thinking about a way to get data sorted in a vertical list.

I have a output list of tickets each has a varible amount of rows. From each row i collect up to 12 items of info form one cell in that row.

Example
Columns
A = ticket ID
B = Ticket text (multiple items)
C = Find in B (something)
D = Find in B (something)
E= Find in B (something)
etc..
N= Find in B (something)

Because each ticket has a varible return rate of rows how can i populate the data into alist based on the following Example

E.G. 3 row return making up 36 individual items of data, as im only looking at tickets one at a time i would like a feature so that i enter a ticket id and all the relevent data in a list is shown this is a full example normally only about half the FINDs are populated) cell O1 = T000001


I thought maybe a Vlookup and offset maybe the way ? but as tickets go from a single row return to 30 rows im unsure how to code this as formulas.

So from this

T000001 1 2 2 3 2 4 5 5 4 2 3 4
T000001 A B C A B C A B C A A B
T000001 59.37055976 24.26022387 44.34553042 51.38593853 26.78266724 71.13265845 13.75672671 32.58321586 20.53248997 91.75043595 66.83605989 73.94375571

To....

Col P, Col Q
T000001 1
T000001 2
T000001 2
T000001 3
T000001 2
T000001 4
T000001 5
T000001 5
T000001 4
T000001 2
T000001 3
T000001 4
T000001 A
T000001 B
T000001 C
T000001 A
T000001 B
T000001 C
T000001 A
T000001 B
T000001 C
T000001 A
T000001 A
T000001 B
T000001 59.37055976
T000001 24.26022387
T000001 44.34553042
T000001 51.38593853
T000001 26.78266724
T000001 71.13265845
T000001 13.75672671
T000001 32.58321586
T000001 20.53248997
T000001 91.75043595
T000001 66.83605989
T000001 73.94375571


Will try the TRANSPOSE array but if anyone else has an idea that would be nice...?

 

[slamanager]

Hi,

Thinking about a way to get data sorted in a vertical list.

I have a output list of tickets each has a varible amount of rows. From each row i collect up to 12 items of info form one cell in that row.

Example
Columns
A = ticket ID
B = Ticket text (multiple items)
C = Find in B (something)
D = Find in B (something)
E= Find in B (something)
etc..
N= Find in B (something)

Because each ticket has a varible return rate of rows how can i populate the data into alist based on the following Example

E.G. 3 row return making up 36 individual items of data, as im only looking at tickets one at a time i would like a feature so that i enter a ticket id and all the relevent data in a list is shown this is a full example normally only about half the FINDs are populated) cell O1 = T000001


I thought maybe a Vlookup and offset maybe the way ? but as tickets go from a single row return to 30 rows im unsure how to code this as formulas.

So from this

T000001 1 2 2 3 2 4 5 5 4 2 3 4
T000001 A B C A B C A B C A A B
T000001 59.37055976 24.26022387 44.34553042 51.38593853 26.78266724 71.13265845 13.75672671 32.58321586 20.53248997 91.75043595 66.83605989 73.94375571

To....

Col P, Col Q
T000001 1
T000001 2
T000001 2
T000001 3
T000001 2
T000001 4
T000001 5
T000001 5
T000001 4
T000001 2
T000001 3
T000001 4
T000001 A
T000001 B
T000001 C
T000001 A
T000001 B
T000001 C
T000001 A
T000001 B
T000001 C
T000001 A
T000001 A
T000001 B
T000001 59.37055976
T000001 24.26022387
T000001 44.34553042
T000001 51.38593853
T000001 26.78266724
T000001 71.13265845
T000001 13.75672671
T000001 32.58321586
T000001 20.53248997
T000001 91.75043595
T000001 66.83605989
T000001 73.94375571


Will try the TRANSPOSE array but if anyone else has an idea that would be nice...?

VLOOKUP is also a way to get this working, Friday arvo and my brain has just kicked in

 

[bebo021999]

If first record of your data from A2 to N2:
At P2:

=OFFSET($A$1,INT((ROWS($1:1)-1)/12)+1,IF(MOD(ROWS($1:1),12)=0,12,MOD(ROWS($1:1),12))+1,)

Fill down