Move data from horizontal to vertical

Originally Posted by JustMe602

HTML Code: 

How do I get the get the lines to transpose vertically without have to do each manually?

So for example I would like to see the data read like this.

TORI TORIE
TORI VICKEY
TORI VICKI
TORI VICKIE
TORI VICKY
TORI VICTORIA

ETC...




Thanks for any input ahead of time.

Hi,


In I1 put

=OFFSET($A$1,INT((ROW()-1)/6),0)

in J1 put

=OFFSET($A$1,INT((ROW()-1)/6),MOD(ROW()-1,6))

formula fill these downwards until you run out of data.

Copy columns I and J and Paste Special = Values.

Remove any unwanted columns

hth
---

Originally Posted by Mark@Work

There is an array transpose function called "TRANSPOSE()" in the "Lookup and Reference" function group.

Select a range of cells the correct size and shape to fit the transposed data,
Type in the function TRANSPOSE(AQ1234:ZY4321),
{replace the above fictitious addresses with the addresses of the top left and bottom right cells of the array you want to transpose}
Press [Ctrl Shift Enter]
{to enter it as an array function which covers the entire selected range of cells},

Mark.

True Mark, but the example given indicates that the first word is repeated down the 6 other transposed values (per current row), thus each current row becomes 6 rows of 2 data items, the first being a repeat.

Well, . . I hope I read it correctly (someone will probably point out that TORI means 'row' or something)

hth
---

Originally Posted by Mark@Work

You would obviously have to move columns I, J to give extra room...

You could perhaps use the COUNT() function to determine how many combinations there are.

Replace the 6 in Bryan's formulas by the number of combinations.

Assume you have the number of combinations saved in cell Z1,

Bryan's formulas become:

=OFFSET($A$1,INT((ROW()-1)/$Z$1),0)
=OFFSET($A$1,INT((ROW()-1)/$Z$1),MOD(ROW()-1,$Z$1))

Mark

HI Mark,

Almost, the second part needs to be

=OFFSET($A$1,INT((ROW()-1)/$Z$1),MOD(ROW(),$Z$1+1))

to allow for the first in a set to be a 'none-line'

the 'short' sets produce zero entries, but they can easily be filtered out when required.

Z1 needs to be the column number of the last column of data, the sample showed 6, but with only 1200 lines, then, after the Text to Columns, in a row after 1200 a simplle =COUNTA(A1:A1200) formula filled sideways will show where the data ends.

The formula, as Mark said, will need to be in a column after the last data item, AA and AB look good.

Originally Posted by mattlazarus

Wow, I was just going to enter the exact same question. Eerie.

Hi,

watch the variation on this question, where the first item does not count as a row, but is the column A entry for all related rows.

---