Moving Rows into specific column...

[Kb24]

Hi.. i have a problem here..
I need to move a number of rows, based on user input
in front of the first column, continuously until it 'flattens out' into one row..
And then do the procedure again for the next row
Kinda hard to explain.. maybe i'll use an example
using this matrix :

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	26	27	28	29	30
31	32	33	34	35	36

If user inputs "3", the matrix becomes :

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	26	27	28	29	30	31	32	33	34	35	36

The 2nd, 4th and 6th row is moved to row 1,2 and 3 respectively


If user inputs "2" the matrix becomes :

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	26	27	28	29	30	31	32	33	34	35	36

2nd row is moved into the first row, then the third row is moved behind them
fifth row is moved into the fourth row, then the sixth row is moved behind them

If "1" then it becomes 1 row :

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	26	27	28	29	30	31	32	33	34	35	36

Does it make sense?

I need to do this for a 1044x1044 matrix.. so i cant do it manually...

thanks.

[DonkeyOte]

First off I would say this only viable given you're using XL2007 - in earlier versions you were limited to 256 columns and given your real 1044x1044 matrix could theoretically need to be transposed into one row you would not be able to do this.

I would say first off that whether you opt for VBA or formulae you're best bet will be to first store your matrix in a vector ... eg using example of A1:F6 you create a vector of A1:A36 on another sheet, it will I think be much easier to work with the vector to create the final matrix than with the original matrix ... ie you could achieve this with formulae...

If you were to create vector impression of initial matrix on Sheet2 where initial matrix Sheet1!A1:F6 like so:

Sheet2!A1:
=INDEX(Sheet1!$A$1:$F$36,1+INT((ROWS(A$1:A1)-1)/6),1+MOD(ROWS(A$1:A1)-1,6))
copied down to A36

And we were then to recreate matrix per row requirement of user, say on Sheet3 then with row requirement entered into A1 (and validated such that it is indeed a multiple of the rows in the vector) then

A1: 3 (entered by user)
B1: =COUNT(Sheet2!A:A)/A1
C1: =MOD(B1,1)
(where C1 is the check that A1 is multiple - ie even spread possible)

we can in turn create the requisite matrix using:

A2: =IF(OR($C$1,ROWS(A$2:A2)>$A$1,COLUMNS($A2:A2)>$B$1),"",INDEX(Sheet2!$A:$A,COLUMNS($A2:A2)+($B$1*(ROWS(A$2:A2)-1))))
applied across matrix - where matrix is square of original
(using example 36 x 36 given could be 1 row or 36 row requirement from user)

I confess I've not tested this out on a large matrix and I suspect performance would be poor and would warrant VBA but I just wanted to illustrate that by using a vector things become a little simpler.

In VBA terms it would I think be simply a case of iterating the vector, selecting the range of values to be copied from the vector and pasting into next blank row on results tab, something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range, lngRowI As Long, dblDiv As Double
On Error GoTo ExitPoint
If Target.Address <> "$A$1" Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Sheets("Sheet2"): Set rngData = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)): End With
dblDiv = rngData.Rows.Count / Target.Value
If Int(dblDiv) <> dblDiv Then
    MsgBox "Invalid Row Requirement - Not Flat", vbCritical, "Error"
    GoTo ExitPoint
Else
    ActiveSheet.UsedRange.Offset(1).Clear
    For lngRowI = 1 To Target.Value Step 1
        Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, dblDiv).Value = Application.Transpose(rngData.Cells(1 + (dblDiv * (lngRowI - 1)), 1).Resize(dblDiv))
    Next lngRowI
End If
ExitPoint:
Set rngData = Nothing
Application.EnableEvents = True
End Sub

the above utilising the Change event on cell A1 such that when A1 is altered the matrix repopulates.

I've attached a file which illustrates all of the above - given this is only viable in XL2007 it is .xlsm format

[Kb24]

Thanks,
I think it's easier with vba code, because i may have to change to portion again..
for the first method;
it works when i put the 6x6 matrix in 'sheet1'
but when i tried it with a 1044x1044 matrix that i'm going to use,
it doesn't work... Did you limit it to certain value?

And for the Vba Method,
i tried to copy the 1044 matrix into a new sheet, then run your code from a command button, but it says "argument not optional"
Do i have to change anything?

[Kb24]

Maybe it's better if i give you the file

http://ifile.it/lw4tgic/floyd_route.rar

Just ignore the first row and column, they are the variable name for the matrix.
thanks.

[DonkeyOte]

please save as .zip and upload here directly.

re: 1044

a) the VBA method still utililsed the vector representation of the matrix which was set to 6x6 so the formula would need to be changed

b) regards moving to a button based event - yes the code would require alteration as presently it's setup to be driven automatically of the worksheet_change event (Target is quite a "particular" argument)

[DonkeyOte]

And in hindsight I'm a doofus, this won't work... even in XL2007 given if you opted to display matrix in one row you would require 1044^2 columns, displaying a matrix of those dimensions as a vector is impossible (by a long way).
In 2007 you're limited to around 16384 columns ... so the least rows you could use in terms of display (given flat requirement) would be 72.

[Kb24]

I've tried to upload it before, but it always failed... maybe because of the size?
It's 2,9 mb...

Well, i guess that will do, the least i need is to change it to 116 rows...
Or flatten each 9 rows into 1.. (1044:9 =116)

So which part of the code that should be changed to do this?
thanks.

[DonkeyOte]

I think I misinterpreted your post in so far as I didn't realise you were looking to do like-for-like ... (thanks also to shg who pointed this out to me privately to save my blushes), perhaps then:

Public Sub Route()
Dim wsInput As Worksheet, wsOutput As Worksheet
Dim rngMatrix As Range
Dim lngComp As Long, lngRowI As Long, lngCopyRow As Long, lngCol As Long
Dim bCount As Byte
Dim xlCalc As XlCalculation
On Error GoTo ExitPoint
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set wsInput = Sheets("Input")
Set wsOutput = Sheets("Output")
With wsInput: Set rngMatrix = .Range("B3:ANE1046"): End With
With wsOutput: .UsedRange.Clear: End With
lngComp = Application.InputBox("Enter Compression - 1 to 15", Default:=1, Type:=1)
Select Case lngComp
    Case 0
    Case 1 To 15
        For lngRowI = 1 To rngMatrix.Rows.Count Step 1
            If Int((lngRowI - 1) / lngComp) = (lngRowI - 1) / lngComp Then
                lngCopyRow = lngCopyRow + 1
                bCount = 0
            End If
            bCount = bCount + 1
            lngCol = 1 + ((bCount - 1) * rngMatrix.Columns.Count)
            rngMatrix.Rows(lngRowI).Copy wsOutput.Cells(lngCopyRow, lngCol)
        Next lngRowI
    Case Else
        MsgBox "Invalid Entry - Routine Terminated", vbCritical, "Fatal Error"
End Select
ExitPoint:
Set rngMatrix = Nothing
Set wsInput = Nothing
Set wsOutput = Nothing
With Application
    .Calculation = xlCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

I have no doubt this could be improved, in my defence it's Sunday morning !

The above would prompt end-user to enter a compression value - ie compress x rows to 1 row ... at most you can compress 15 rows to 1
(15 * 1044 = 15660 columns, 16 being 16704 and thus exceeding available space of 16384 in XL2007)

[Kb24]

Thanks,

I solved this problem already actually..
By filtering the multiplication of 9..
So i moved every nth row to another sheet then join them again

But of course it's still easier using Vba, so i'm still gonna use your code ..

thanks for your help.