Content Rotation from column to column

[rize1159]

I have a work sheet containing first name in A1 and last name in AA1. Now I want to arrange the these words in a specific order by of column differences. Let suppose A1=X,B1=Y,C1=Z, D1=W, E1=Q
we will prepare second row by keeping first letter constant at its place and replacing the others by constant column difference Like:

A1=X,B1=Z,C1=Q, D1=Y, E1=W

first letter is kept constant and second comes from third column of last row. this process will continue till all 28 names are formed in the same way. the column difference will be 1 to 28.

 

[rize1159]

Following is an example of what I want to do. First rotation is by the difference of one column. and second row is with the difference of two columns and till the difference raises to total number of total column minus one as you can see. You can see the example and cell reference can't be used. I have 28 names and they can be arranged in 784 ways if first name is changed after 28 arrangements.

1 a b c d e
2 a c e b d
3 a d b e c
4 a e d c b
5 a b c d e
second example

1b a c d e
2 b c e a d
3 b d a e c
4 b e d c a
5 b a c d e

 

[cgcamal]

Hi rize,

I'm not clear so far.

When you say difference of columns, is refering to column number?
I mean, Col_Number of X=24, Col_Number of Y=25, etc...

From which columns number do you do a difference to obtain the 2nd row order, 3rd row order, etc?
Maybe a step by step example for every rotation for 2nd row and 3rd?

Sorry, I just don't get it yet

Regards

 

[rize1159]

Pz check this one:

1,2,3,4,5 are row numbers.
1 & 5 are the same.
1 is the original row.

contents of the first column are same through out just as in row 1. Capitals are columns as in excel
A B C D E

b a c d e

lets make second row. A2 will remain same as A1. Now second letter in row two (B2) will be from column A+2 means column C (C1), C2 will be A+4 or C+2 (E1). Now column finished.D2 will be A+6 or E+2. Counting here will be done in cyclic way here so D2=B1 & similarly E2=D1. Here goes the second row:
2 b c e a d

For third row. A3=A1, B3 we be again from first row(A1:E1).,this time difference in column counting will be three i.e B3=D1, C3=B1 as cyclic counting. D3=E1 & E3=C1.
3 b d a e c

For fourth & fifth row, process will remain same except difference will be repeated for 4th column first row and 5th column. Counting will continue in cyclic way till we get the first row again.
4 b e d c a
5 b a c d e
May be a simple formula would not help rather a code would be required.

 

[rize1159]

it is a cross post and still not resolved following are links

http://answers.microsoft.com/en-us/...c-68b599b31bf5?page=1&tm=1304397532443#footer

http://www.mrexcel.com/forum/showthread.php?p=2703566&posted=1#post2703566

http://www.ozgrid.com/forum/showthread.php?t=153127&p=553514#post553514

http://www.excelforum.com/excel-wor...tation-from-column-to-column.html#post2518589

http://www.thecodecage.com/forumz/m...8817-cell-content-rotation-column-column.html

 

[cgcamal]

Hi rize,

The following macro it seems to work for your examples, when number of columns is an odd number (Try it in a copy of your original data).

Then, may you show an example of how would be the rotation when we have a even number of columns?

Sub Rotate_Columns()
[COLOR=Green]'César C, 03/May/2011
'Macro to rotate colums in specific order
'If number of data are in N columns, where N is an odd number, this macro
'will use temporaly columns Nx(N-1) columns to the rigth only in first row[/COLOR]

Application.ScreenUpdating = False
Lc = Range("A1").End(xlToRight).Column

Range(Cells(1, 1), Cells(1, Lc)).Copy

For k = 1 To Lc - 1
    Cells(1, k * Lc + 1).PasteSpecial xlPasteValues
Next
    Application.CutCopyMode = False

For i = 2 To Lc
    Cells(i, 1) = Cells(1, 1)
 If i < Lc Then
    For j = 2 To Lc
        Cells(i, j) = Cells(1, 1 + i * (j - 1))
    Next
 Else
    For j = 1 To Lc
      Cells(i, j) = Cells(1, i + j)
    Next
 End If
Next
    Range(Cells(1, Lc + 1), Cells(1, Lc * Lc)).ClearContents
    
Application.ScreenUpdating = True
End Sub

Regards

 

[rize1159]

you are great and you have done a great job for me but there is one problem.

The macro failed on real data of 28 columns giving an error that copy and paste area are not equal. When I looked at the situation, this macro requires a 756 column for its working on 28 cells. and excel dot have so many columns. & generates error. I have tried it on above example of A to E. it really worked very good

I think if rather than using temporary column, we use temporary rows far below the actual results, then it can be done.

But it was really nice that a hill has gone in movement for me.

 

[cgcamal]

Hi Rize,

Great at list it works partially.

I´ll try to do my best to avoid using temporary columns or use temporary rows instead of that.

Meanwhile, may you show an example when we have for example 6 columns (even number of columns).

Best regards

 

[rize1159]

Here goes the example for even number six columns. zero & 5 are same. %the row is confirmatory. Now the special question here is how to handle if the same latter results in cyclic counting. The answer is we will ignore the number and take the next one and start counting. Explanation is given here:

Take row 1: A2=A1, B2=A1+2, C2=C1+2, Now D2 should be equal to E1+2 but this will result in "a" which is wrong because we can't write any letter for the second time in the same row as this will result in elimination of some other letter. So we will leave "a" and take the next one i.e "b". Now E2=B1+2, F2= D1+2.

Row=2
A3=A1, B3=A1+3, C3=D1+3 but this will generate the same letter, so we will take the next letter "b". D3=B1+3, E3=E1+3,but again repeating letter i.e "b" so we will take next one i.e "c", & F3=C1+3

0 a b c d e f
1 a c e b d f
2 a d b e c f
3 a e c b f d
4 a f e d c d
5 a b c d e f

 

[cgcamal]

Rize,

Try this one, now is not using temporary columns anymore, but is still working for odd number columns.

Sub Rotate_Columns2()
[COLOR=Green] 'Macro to rotate colums in specific order for odd number of columns[/COLOR]

Dim FirstRowCols() As Variant
Dim Lc As Integer

Application.ScreenUpdating = False
Lc = Range("A1").End(xlToRight).Column

ReDim FirstRowCols(1 To Lc ^ 2)

For m = 0 To Lc - 1
    For n = 1 To Lc
        FirstRowCols(n + m * Lc) = Cells(1, n)
    Next
Next

For i = 2 To Lc
    Cells(i, 1) = FirstRowCols(1)
 If i < Lc Then
    For j = 2 To Lc
        Cells(i, j) = FirstRowCols(1 + i * (j - 1))
    Next
 Else
    For j = 1 To Lc
      Cells(i, j) = FirstRowCols(i + j)
    Next
 End If
Next
    
Application.ScreenUpdating = True
End Sub

I'll give a try with your example with even number of columns now.

Regards