Multidimensional Array to Single Column Range

[Jonathan78]

Hello all,

I am trying to put a multiple column range into an array and write it back to a single range.
This is what I have so far but can't get it working properly.

I see what the problem is but am not experienced enough with arrays to solve it
It overwrites 7 times the cell value before going to the next row.

Please Login or Register  to view this content.

All help is welcome.

[kalbasiatka]

Hi. No one but you knows what should be the result. Show in the file what needs to be done with an array.

[Akuini]

By 'single range' you mean single column, right?
Maybe this:

Please Login or Register  to view this content.

[jindon]

For each loop in 2d array goes downwards first...

Please Login or Register  to view this content.

[pike]

Hi,
or alternative
https://excelxor.com/2014/08/13/sing...-1-rows-first/

[Jonathan78]

By 'single range' you mean single column, right?
Maybe this:

Please Login or Register  to view this content.

Thanks Akuini this is working great!

[Jonathan78]

For each loop in 2d array goes downwards first...

Please Login or Register  to view this content.

Thanks jindon, I go for akuinis solution because I found yours harder to understand

[Jonathan78]

Hi,
or alternative
https://excelxor.com/2014/08/13/sing...-1-rows-first/

Thanks Pike but I needed a VBA solution

[Doc.AElstein]

So here is another solution to add to the collection

But First.......
_1)

As kalbasiatka said No one but you knows what should be the result. It is always best to demonstrate to us ( preferably with reduced size, desensitised data ) exactly what You want

Both Akuini’s and jindon codes work for me.
Jindon’s goes ( strangely ? ) through all rows first, then columns. ( I thought VBA and Excel always went from right to left then top to bottom? Maybe a Arr is different)

Akuini’s is made to go through each column before going down a row.

I do not feel like doing Pike’s solution way just now, ( although at first glance it does seem to be explained very well ), but I would like to see it applied to your application as another interesting solution, How about it Pike ?

My Guess was that you wanted rows then columns, as you have confirmed now. My Code does that.
Note All codes just give you Values Out

To demo first on reduced data for our three codes.

Say this is your Data

Using Excel 2007

Row\Col	F	G	H
21	f21	g21	h21
22	f22	g22	h22
23	f23	g23	h23

pgcArraysSplitToColumn

_.....

then using a version of code from Akuini

Please Login or Register  to view this content.

You get this:

Using Excel 2007

Row\Col	AX
1	f21
2	g21
3	h21
4	f22
5	g22
6	h22
7	f23
8	g23
9	h23

pgcArraysSplitToColumn

_...............................................
Or
using a version of code from jindon

Please Login or Register  to view this content.

You get

Using Excel 2007

Row\Col	AX
1	f21
2	f22
3	f23
4	g21
5	g22
6	g23
7	h21
8	h22
9	h23

pgcArraysSplitToColumn

_...........................................
_... OK back to mother code alternative.

_....
_2 ) Here is my equivalent code ( to Akuini’s) . It gives the same results as Akuini’s
Here for the reduced input Range
Main Code for reduced Data

Please Login or Register  to view this content.

Here the code for what we guessed was your wanted Output Range
Main Code for Full input Range

Please Login or Register  to view this content.

As you can see the difference is very little, but the reduced example is better to follow and understand in the Thread.
My Codes are very flexible: You just change the
Input range
and
Top Cell Range
of where the output should go.

Note: For both my codes you need also this Function. Just copy it to the same Module that your main code is in. There are simple Functions to do that BTW. Just that seemed the quickest. Many of those are explained in detail here:
http://www.excelforum.com/tips-and-t...explained.html

A Function to get Column Letter from Column Number

Please Login or Register  to view this content.

_..................................................

There you go... or almost....

.....I go for akuinis solution ......because I found yours harder to understand

I do not like to use codes I do not understand. Mine is very hard to understand , but if you can you would learn a lot. For the benefit of anyone interested, in the next post I explain it in full detail, ( For ease of explanation it works on the reduced size data I gave in the first screen shot above.

[Doc.AElstein]

I do not like to use codes I do not understand. Mine is very hard to understand , but if you can you would learn a lot. For the benefit of anyone interested, in this post I explain my Code in full detail, ( For ease of explanation it works on the reduced size data I gave in the first screen shot above.

So the Explnation of Alan’s codes.
Code also here:
http://www.excelforum.com/showthread...10#post4381972

In words first briefelly.
To Fully understand , run the code in F8 debug mode, and follow through, the comments and what happens in the Worksheet. Also if you know how set watches on variables ( Highlight variable in code anywhere and hit SHIFT + F9 ), do that and see how they are filled

In Words.

The basic idea is to simply use the .Index Function in this form, ( “pseudo code” )

Output = .Index( Cells , rws(), clms() )

Cells is the entire spreadsheet Range

rws() and clms() are Arrays containing the spreadsheet row and column number you want starting from the first.

VBA .Index works in such a form such that it pairs sequentially each row and column number , then gives out the Value at those Spreadsheet co ordinates. This value it adds to the end of an ever increasing internally generated 1 D Array
VBA has a convention to allow you to paste this “long” Array of values to a Row.
But you do not want that. So we simply .Transpose that to give you a “deep” column
( I would add something in passing here. The .Transpose can be a bit quirky when transposing Values. So you might want to replace it with a simple Function. Such as here
http://www.excelforum.com/showthread...t=#post4378086
Note as you are giving a 1 D Array you will need to use the biggest code:
http://www.excelforum.com/showthread...t=#post4378089
_....)

Rem Ref pgc http://www.mrexcel.com/forum/excel-q...-column-2.html
Rem Ref shg http://www.excelforum.com/tips-and-t...ml#post4214733
_...........

So here the code , have fun in going through !!

Alan

Please Login or Register  to view this content.