| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Returning multiple text data into 1 column from many column entrie | Excel Worksheet Functions | |||
| Plotting multiple Y column data versus single X column in Excel 20 | Charts and Charting in Excel | |||
| Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
| Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
| Problem when trying to convert one column with multiple rows to one row with multiple column | Excel Programming | |||
Linear Mode
