one column to multiple
 

I have one very long column, with 14 repeating rows for different printers.
I would like each printer on a row, so 14 elements side-by-side for each
item. This way I can sort by one item, etc.

Can anyone help me with a macro to transpose the info this way.

Thanks,

Lisa

one column to multiple
 

With all credit to Biff

With your values in column A, type this in B1:

=INDEX($A:$A,(ROWS($1:1)-1)*14+COLUMNS($A:B)-1)

and copy it through to O1.

Then copy B1:O1 down the page as far as you think you'll need them.

Your blocks of 14 repeating values will now appear in rows across th
page.

Regards
Mik

--
Mikeopol
-----------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...fo&userid=1857
View this thread: http://www.excelforum.com/showthread.php?threadid=57135

one column to multiple
 

Hi,

I get the value in cell A1 14 times, but not the 14 values across the one row.

Lisa

"Mikeopolo" wrote:


With all credit to Biff

With your values in column A, type this in B1:

=INDEX($A:$A,(ROWS($1:1)-1)*14+COLUMNS($A:B)-1)

and copy it through to O1.

Then copy B1:O1 down the page as far as you think you'll need them.

Your blocks of 14 repeating values will now appear in rows across the
page.

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

Can you please "cut/post" the formula you have in B1?

Many thanks
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

B1 has =INDEX($A:$A,(ROWS($1:1)-1)*14+COLUMNS($A:B)-1)
C1 has =INDEX($A:$A,(ROWS($1:1)-1)*14+COLUMNS($A:C)-1)
and each one is taking 2 cells, so the first is taking B1 and B2, the next
C1 and C2

Lisa


"Mikeopolo" wrote:


Can you please "cut/post" the formula you have in B1?

Many thanks
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

If you press F9 does the result change?

If so, your calculation was set to manual - change to automatic (tools,
Options, Calculation, and set to auto.

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

One more prablem, it is still taking up 2 rows, so the formala is taking B1
and B2, not just B1.

Lisa

"Mikeopolo" wrote:


If you press F9 does the result change?

If so, your calculation was set to manual - change to automatic (tools,
Options, Calculation, and set to auto.

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

Sorry, just the cut and paste from the post made the extra rows, it work now!

THANKS!

Lisa

"Lisa" wrote:

One more prablem, it is still taking up 2 rows, so the formala is taking B1
and B2, not just B1.

Lisa

"Mikeopolo" wrote:


If you press F9 does the result change?

If so, your calculation was set to manual - change to automatic (tools,
Options, Calculation, and set to auto.

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352

one column to multiple
 

Excellent!
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571352