Moving Rows into specific column...

**Kb24** · 09-10-2009, 09:04 PM

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 :

Please Login or Register  to view this content.

If user inputs "3", the matrix becomes :

Please Login or Register  to view this content.

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

If user inputs "2" the matrix becomes :

Please Login or Register  to view this content.

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 :

Please Login or Register  to view this content.

Does it make sense?

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

thanks.

**DonkeyOte** · 09-11-2009, 03:10 AM

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

we can in turn create the requisite matrix using:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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** · 09-11-2009, 07:33 PM

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** · 09-12-2009, 01:10 AM

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** · 09-12-2009, 01:35 AM

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** · 09-12-2009, 02:52 AM

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** · 09-12-2009, 06:27 PM

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** · 09-13-2009, 05:14 AM

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:

Please Login or Register  to view this content.

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** · 09-13-2009, 06:52 PM

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.