two dimensional array to one column

[CR Optiker]

I have a two dimensional array of data - a total of about 40,000 cells. I'd
like to reformat the data into one column: A1, B1, C1...FE1, A2, B2,
C2...FE2, A3......etc order in the column.

Is there a relatively easy way to do that? I'm not strong on matrix
operations, nor scripting macros, so am looking for a relatively simple
combination of functions that will do it.

Thanks!
Optiker

 

[Ken Wright]

What range is your data in currently?

 

[CR Optiker]

What range is your data in currently?

A1 to FE305 to transform into A1 to A49105. I have this for each of three
worksheets. From there, I can integrate data into one worksheet, three
columns of 49105 rows each. This data then represents a 3D cloud of points,
and it's this cloud that I want to be able to visualize. Will probably try
plotting in another app since I'd be surprised if Excel's plot function
will handle it.

Optiker

 

[Anders S]

Hi Optiker,

Below is a macro that I hope does what you want. It inserts a new column A, then copies the original data column by column, row by row into the new column A.

Tried to test it with a 161x305 but gave up after 15 minutes, macro still ticking though. I have a slow computer with not very much memory. Macro works fine on a smaller range.

'-----
Sub TwoDtoOne()
Dim rowCount As Integer, colCount As Integer
Dim currRow As Integer, currCol As Integer
Dim destRow As Long
Dim destRange As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Range("A2").CurrentRegion
rowCount = .Rows.Count
colCount = .Columns.Count + 1
End With

destRow = 1
Range("A1").EntireColumn.Insert
Set destRange = Range("A:A")

For currRow = 1 To rowCount
For currCol = 2 To colCount
destRange.Cells(destRow).Value = Cells(currRow, currCol).Value
destRow = destRow + 1
Application.StatusBar = destRow
Next
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
'-----

HTH
Anders Silvén

 

[Harlan Grove]

A1 to FE305 to transform into A1 to A49105. I have this for each of three
worksheets. From there, I can integrate data into one worksheet, three
columns of 49105 rows each. This data then represents a 3D cloud of points,
and it's this cloud that I want to be able to visualize. Will probably try
plotting in another app since I'd be surprised if Excel's plot function
will handle it.

If your source data were in SomeWorksheet!A1:FE305 named Source, then in another
worksheet's A1 cell enter the formula

=INDEX(Source,INT((ROW()+160)/161),MOD(ROW()-1,161)+1)

copy and paste into A2:A49105. No need for macros.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Sub test1000()
Dim rng As Range, arr As Variant
Set rng = Range("A1:FE305")
arr = ArrayReshape(rng, 49105, 1)
Range("A1:A49105").Value = arr
End Sub

4 seconds.

Alan Beban

 

[Gord Dibben]

Optiker

Assuming data starts at row 1 of column A.

Sub rowstocol()
Dim colnos As String
Dim CopytoSheet As Worksheet
If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Range("A1").Select
With Selection
.EntireRow.Insert
Range("A1").Value = " "
End With
Set Wks = ActiveSheet
Application.ScreenUpdating = False
For Each wkSht In Worksheets
With wkSht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
Wks.Activate
Range("A1").Select
Restart:
On Error Resume Next
colnos = InputBox("Enter Number of Columns to Transpose to Rows")
If colnos = "" Or colnos < 2 Then
Style = vbYesNo
msg = "You Have Cancelled This Operation" & Chr(13) _
& "Or Entered Less Than 2 Columns" & Chr(13) _
& "Do You Wish To Try Again?"
response = MsgBox(msg, Style)
If response = vbYes Then GoTo Restart
If response = vbNo Then Exit Sub
Else

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End If
End Sub

Gord Dibben XL2002

 

[Gord Dibben]

Column FE is 161 in case you haven't counted.

Gord


Gord Dibben XL2002

 

[Anders S]

Haha!

4 seconds, I like that. Will try it tomorrow.

Best regards
Anders Silvén

 

[Anders S]

Harlan,

No need for macros.

Of course not. I went for a macro solution because I thought 50,000 formulas would load down the application more than a macro entering values in the cells. Didn't compare the difference though.

Tried your formula several times, it yields "1" for me all the way to row 50,000. Will look into it tomorrow.

Best regards,
Anders Silvén

 

[Harlan Grove]

Any chance you could post in truly plain text rather than MIME plain text?

. . . I went for a macro solution because I thought 50,000 formulas
would load down the application more than a macro entering values in
the cells. Didn't compare the difference though.

You need to test these things then. Perhaps my PC is faster than yours, but
it took about 2 seconds to fill Sheet1!A1:FE301 with the formula

=1000*ROW()+COLUMN()

Then I named this Source, entered my formula in cell A1 in another
worksheet, copied A1, and selected A2:A48461 on that other worksheet. Then I
pasted, and it took about another 3 seconds for the operation to complete.
That your macro took forever given your single cell at a time approach is no
surprise.

Tried your formula several times, it yields "1" for me all the way
to row 50,000. Will look into it tomorrow.

Don't know how you mangled it. The formula works.

 

[Harlan Grove]

Sub rowstocol()
Dim colnos As String
Dim CopytoSheet As Worksheet
If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Range("A1").Select
With Selection
.EntireRow.Insert
Range("A1").Value = " "
End With
Set Wks = ActiveSheet
Application.ScreenUpdating = False
For Each wkSht In Worksheets
With wkSht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
Wks.Activate
Range("A1").Select
Restart:
On Error Resume Next
colnos = InputBox("Enter Number of Columns to Transpose to Rows")
If colnos = "" Or colnos < 2 Then
Style = vbYesNo
msg = "You Have Cancelled This Operation" & Chr(13) _
& "Or Entered Less Than 2 Columns" & Chr(13) _
& "Do You Wish To Try Again?"
response = MsgBox(msg, Style)
If response = vbYes Then GoTo Restart
If response = vbNo Then Exit Sub
Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End If
End Sub

....

Do you have the faintest memory of the vaguest hint of a clue how long it
would take for this to run? Use of .Select in to do this is an almost
criminal waste of processor time. I can only hope that your macro was an
attempt at irony that I'm too dim to appreciate.

 

[CR Optiker]

I have a two dimensional array of data - a total of about 40,000 cells. I'd
like to reformat the data into one column: A1, B1, C1...FE1, A2, B2,
C2...FE2, A3......etc order in the column.

Is there a relatively easy way to do that? I'm not strong on matrix
operations, nor scripting macros, so am looking for a relatively simple
combination of functions that will do it.

Thank you all for your replies. Harlan's solution looks easiest to try,
then Alan's, then Anders, then Gord. Will try them in that order.

Working in parallel while waiting for replies yesterday afternoon, saved
each of three worksheets out to a text file, then wrote a fairly short VB
program to read the three text files and rewrite the data to a text file in
3 column x around 40,000 rows array. It worked, but was fairly
slow...several minutes to do the whole thing. I have a number of these to
do, so my solution would work if I had to do it that way, but I'm hoping
Harlan's solution will do the job.

Incidentally, to put it in perpsective, each of three worksheeds contains a
2D array with values of 0 to 255. Each worksheet represents the 8-bit
values of one of three color channels of a color image - in the case I'm
workin gon, its the HSI system, so one worksheet for hue, one for
saturation and one for intensity, but it could just as easily be the RGB
system or others. The three worksheets then describe the color image pixel
by pixel. What I'm doing is reformatting to simply put all of the pixels
into three columns, one for each color channel, and one row per pixel so I
can do other processing on the array that this format will facialitate.

Thanks again!
Optiker

 

[CR Optiker]

If your source data were in SomeWorksheet!A1:FE305 named Source, then in another
worksheet's A1 cell enter the formula

=INDEX(Source,INT((ROW()+160)/161),MOD(ROW()-1,161)+1)

copy and paste into A2:A49105. No need for macros.

Harlan...Not working. I must be doing something wrong. The array size was a
test case...have reduced size some for real data. Currently, 2D array is on
a worksheet named "Surce" and blank second worksheet named "one column".

In Source, array is 125 columns x 300 rows = 37,500 cells.

In the "single column" worksheet, in A1 I entered your equation. It choked
and told me I had an invalid name, then somehow I stumbled onto the dialog
box where you point to the location of entries in the equation and found
that what it didn't like was the name Source in the syntax you show, but
wanted to see the following suntax.

=INDEX("Source!",INT((ROW()+124)/125),MOD(ROW()-1,125)+1)

which is the current form of my equation. Notice that I've substituted 124
for your 160 and 125 for your 161 since 161 referred to the number of
columns in my previous set and in my current set, that value is 125. With
the equation in this form, I get a cell entry #VALUE! and error message
telling me I have an error in some value. I tried to use the step-through
evaluation, but the first step says that en error in the value will occur
on the next step, adnsince I've never doen this before, I don't know how to
interpret that and proceed. It shows a reference...

'one column'!$A$1 = INDEX("Source!",1,1)

where the stuff on the right side is underlined and the "1" before the last
parenteses is in italics. The note at the bottom of the box says that "the
next evaluation will result in an error." Clicking the "Evaluate" button
then shows the result of the underlined calculation which is the #VALUE! in
teh cell.

As far as I can tell, it's still choking on the name of the Source page, or
its syntax. Since I don't understand how the Index function works, I'm
having trouble debugging. Suggestions? Incidentally, I'm running Excel
2002-SP1.

Additional help greatly appreciated!
Optiker

 

[Alan Beban]

Currently, 2D array is on
a worksheet named "Surce" and blank second worksheet named "one
column".

In Harlan Grove's solution, "Source" is the name of the range, not of
the worksheet.

Alan Beban

 

[Anders S]

Harlan,

Your formula works just fine. My mistake, sorry about that.

Anders

No need for macros.

Of course not. I went for a macro solution because I thought 50,000 formulas would load down the application more than a macro entering values in the cells. Didn't compare the difference though.

Tried your formula several times, it yields "1" for me all the way to row 50,000. Will look into it tomorrow.

Best regards,
Anders Silvén

 

[CR Optiker]

In Harlan Grove's solution, "Source" is the name of the range, not of
the worksheet.

Alan Beban

Alan...Ah, I think I see. Thanks! Will try again.
Optiker

 

[CR Optiker]

I have a two dimensional array of data - a total of about 40,000 cells. I'd
like to reformat the data into one column: A1, B1, C1...FE1, A2, B2,
C2...FE2, A3......etc order in the column.

Is there a relatively easy way to do that? I'm not strong on matrix
operations, nor scripting macros, so am looking for a relatively simple
combination of functions that will do it.

Thanks all! I finally got the syntax right in the function suggested by
Harlan, and it worked fine.

An interesting note on speed though. I am running a P4/1.6GHz, 1 GB RAM
Dell under Win2k. It's pretty heavily loaded, so a lot of reasons to be
slow.

I ran one set - an array of 125 x 300 = 37,500 cells, into a single column,
37,500 rows. Once I set up the equation in the first cell, copied it, then
from PASTE into the the other 37,499 cells until it was finished was not
too bad - about 47 sec. But, the whole job at this point was 12 such arrays
into 12 columns - a total of 450,000 cells. Once I set up the first cell in
each column, copied the group, selected the target cells and hit PASTE...
well, let's just say its been running 23 minutes and is still going. The
Excel window is locked, so I can't tell from teh progress bar how far along
it is, but looking at the processes running, it is dominating at 95% of CPU
usage, and is still running. I'll hold this note till it's done to report
the time, then post. Elapsed time = 26 minutes, within a few seconds, and
the numbers don't check - go to pot at the end of the first roughly 300
rows, so apparently I still don't have the syntax right. WIll keep trying
to test it out for the next data set.

In the meantime, thanks to Harlan for the solution, and to Alan for
pointing out my dumb mistake in not understanding Harlan's obvious
notation.

Optiker

 

[Tushar Mehta]

I don't know what else is in that workbook or in other open workbooks,
but transposing 37,500 cells into a single column took a blink of an
eye on my P4 512MB computer.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[CR Optiker]

I don't know what else is in that workbook or in other open workbooks,
but transposing 37,500 cells into a single column took a blink of an
eye on my P4 512MB computer.

interesting...not much else running, but it is a large spreadsheet, and has
other formulas working on large sets. Maybe I have auto-recalc turned on
and it's recalculating every formula in every worksheet - that could make a
difference, but I'm guessing the problem is elsewhere since even that
shouldn't result in such a long process time.

Thanks for the reply...Optiker