Changing left to right, top to bottom

[knitknotes]

Knitting patterns in written form read like normal text, left to right, and top to bottom. We like to convert those patterns to charts, but they read from the bottom up, from right to left. So I need something that converts


ABCD

EFGH

IJKLM

NOPQ


to


QPON

MLKJI

HGFE

DCBA


The transpose command in Paste Special changes rows to column, so that doesn't help. Your ideas are appreciated.

 

[keymaster]

Hi Knitknotes, Welcome to Chandoo.org forums and thanks for the interesting question.


You can use a simple VBA UDF to reverse text in a cell and then INDEX formula to flip it vertically.


See this file with a solution.


http://img.chandoo.org/playground/knitting-reverse.xlsm


For more on reversing a list, refer to http://chandoo.org/wp/2009/11/19/reverse-a-list-in-excel/

 

[knitknotes]

Sorry for the late note of thanks, Chandoo. I've tried it, and it works.


Now to add something I should have said in the first place. ABCD are each in different cells, not a text string in 1 cell. Could you reverse multiple cells?

 

[NARAYANK991]

Hi ,


To reverse a set of cells , you can use the OFFSET function ; for example , suppose the following are the text strings in cells L16 through S16 :


Blessed are they that believe they are blessed


Suppose you enter the following formula in L18 , entered as an array formula , using CTRL SHIFT ENTER :


=OFFSET($L$16:$S$16,0,COLUMNS($L$16:$S$16)-COLUMN(L16)+COLUMN($L$16)-1)


In cells L18 through S18 , you should see :


blessed are they believe that they are Blessed


Narayan

 

[Debraj]

Hi Knitknotes,


I assume your data is in below format

[pre]

1	2	3	4	5
6	7	8	9	10
11	12	13	14	15
16	17	18	19	20
21	22	23	24	25

[/pre]

Use the below Formula in a Cell and copy the same in a 5 x 5 Matrix, by set the formula cell in Top Left..or Drag toward Left 5 and down 5.


Change all 5/E 's to 4/D or 6/F, according to your needed matrix, only in the highlighted area, where Un-highlighted area is your source of data.


=INDEX($A$1:$E$5,ROWS($A$1:$A$5)+1-ROW(A1),COLUMN($A$1:$E$1)+5-COLUMN(A1))


Please let us know if you are facing any problem in applying..


Regards,

Deb

 

[knitknotes]

Deb - thanks so much for this. I tried it, changing the numbers to k2tog SSK YO and all the other codes we use, and enlarged the data to 6 rows/7 columns and it works beautifully. This is going to be a big help for those of us knitting lace from vintage pattern books - now these charts will be far less tedious. Your help is much appreciated.


Narayan - I typed my text into different cells, changed the formula references, and that too worked for me. Many thanks.


To all 3 of you - thanks for this. I couldn't have done it on my own.

 

[Debraj]

Hi Knitknotes!


Thanks for your those kind words.. These kind words are the only reward's which motivates us to help you better..


Regards,

Deb