Free Microsoft Excel 2013 Quick Reference

Array to single column

I'm sure this has been discuss before, but It was not quite what I
want to do, I want to go from

A D G J M
B E H K N
C F I L O to

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

newbie excel user, TIA


Post your answer or comment

comments powered by Disqus
Hi,

I need a macro that allows me to select a matrix of cells and convert it into a single column. My matrix is like so:

1a 1b 1c 1d 1e 1f
2a 2b
3a 3b 3c 3d

I want to change this into a single column like this:

1a
1b
1c
1d
1e
1f
2a
2b
3a
3b
3c
3d

and paste it in a selected location. Thanks so much.

I have data on 10 colmns. How to make all 10 columns data to single column only? i.e. append 10 columns data to only one column.

Appreciate any help!

Hello,

I am new to VB with Excel 2002 and I am a lost goose right about now.

I am trying to move different columns of text data to a single column
i.e. Column E to column A. Col D to Col A. Col C to Col A. and Col
to Col A. Each column has different size rows. The columns and ro
sizes change from day to day, so I need a macro that will recogniz
different size columns and rows.

Today I have columns A through E with different length rows.

I am not sure how to identify the array size, since it will be changin
each time I export the data from another database. Once I have the dat
in Excel then I need to automatically move
( cut and paste ) or whatever, to populate column A with all the dat
from the other columns. I do not care how the data is stacked initiall
in column A. I can do a sort, in the macro, once the data is in colum
A.

I performed this task with the Excel VB recorder and came up th
following code for known cells or array size. My problem is that th
array size, i.e. column and row size will change each time I export ne
data from other database.

Sub MoveColumnsToA()
'
' MoveColumnsToA Macro
' Macro recorded 2/26/2004 by willik
'

'
Range("E1:E11").Select
Selection.Cut
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 61

First off, I want to say that I've searched the site and tried to use different macros that I've found and adapt them to my needs, but so far I have been unsuccessful, so any assistance anyone can provide is appreciated. Now to what I'm trying to do:
I have a sheet that I have made formulas for that I now need to transpose certain rows into columns. Example: Row BV36:CK200 needs to be transposed into a single column at EJ36. There may be blank cells in the row depending on the formula. I would like to be able to select the range that will be transposed and also select the destination, but if that is not possible I understand. There are other ranges besides the one above but I believe if I can get one for that one (hopefully with comments) that I can then adapt it to my needs.
Again, thank you for taking a look and if any more info is needed please let me know. Thank you!

I have numbers in a range over an indeterminate number of rows and 6 columns. I want to create a single column of the numbers with no blank cells in between. It's to check Visa receipts from different depts.

Hope you can help.

Thanks in advance

How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional.

I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.

Hello all, I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous).

I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column.

So basically, this would take the date from several different categories and create a single calendar of events.

Thank you so much for your help!

I have a table with data as follows (commas separate columns)

1,2,3,4,5,6
2,3
1,7,8,4,2,8,3,10,123
34,6
1
3

So there are a variable number of columns with numbers in each row, and the numbers are entered in contiguous columns (there are no intervening 'empty cells' between cells in a single row.

And I would like to turn it into a single column like so:

1
2
3
4
5
6
2
3
1
7
8
4
2
8
3
10
123
34
6
1
3

I would like to convert this data to a single column of numbers. The order the numbers end up in is not important, just that they are in a single column. I have about 9,000 rows of data with anywhere between 1 and 14 entries per row.

Thanks in advance for any help,
-Eric

Hi Guys,

I have a spreadsheet which has data in a number of columns which I need
to copy to another sheet in one single column.

What I need to be able to do is select all the columns from the source
sheet (this number may vary), and then have select a start point in
another sheet and ahve all the columns sequentially pasted in.

I've started to write a macro to do this but haven't got very far, can
any help me out?

Thanks

Simon

Hi All,

I have a Row of numeric data ( in individual columns) that I would like
returned to individual cells in a single column.

My data starts in Column C Row 13 to Column DV Row 13. However, every other
Column in the Row of data is blank. Is it possible to return the data
excluding the blank columns, so there are no blank cells when the data is
returned to a single column?

Sample Data Layout:
C13 BLANK, D13 70, E13 BLANK, F13 45, G13 BLANK, H13 50, I13 BLANK, J13 63,
K13 BLANK etc.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Hello,

I need assistance copying the below cells from one sheet to another. The challenge I am facing is getting from multiple columns to a single column.

Thanks,

Steve

sheet1------sheet2
a1----------a1
a2----------a2
b1----------a3
b2----------a4
c1----------a5
c2----------a6

Hi All,

I have a Row of numeric data ( in individual columns) that I would like
returned to individual cells in a single column.

My data starts in Column C Row 13 to Column DV Row 13. However, every other
Column in the Row of data is blank. Is it possible to return the data
excluding the blank columns, so there are no blank cells when the data is
returned to a single column?

Sample Data Layout:
C13 BLANK, D13 70, E13 BLANK, F13 45, G13 BLANK, H13 50, I13 BLANK, J13 63,
K13 BLANK etc.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Does anyone know how to do this? I have lots of columns that need to be combined into a single column. Any ideas appreciated, I have looked in all the forums but cant quite find the right answer.

Thanks

Beatrice

Hi there. Very novice user here. I'm trying to transpose multiple rows of data into a single column. I've attached an example of the data I'm working with.

Hi there. I provided an example of the output on the sheet marked 'Transposed". What I'm really interested in is transposing the values. I can add the other columns of data/dates etc. once I have the values transposed and in a single column. Note that the values are hourly, so there are always 24 values per day. The sheet labled 'Raw' contains the original data-set, and each day is broken into four rows of six values in each row. What I did to create the example is copy the first row of six values and transpose them into a single column, then copy the second row and transpose/paste these onto the bottom of the single column and so forth.

Randy

Hello,

I've got a spreadsheet with multiple worksheets. Ultimately with VBA code i would like to copy Column B from each worksheet into 1 blank column of a summary sheet.

Example: I would like to copy cell values Column B from the Europe and Asia Worksheets below and paste into a blank country column of the summary Worksheet.


	VB:
	
 
Europe Worksheet 
COL A          Country          GDP 
_                  France          14 
_                  Germany        12 
_                  England          5 
 
Asia Worksheet 
COL A          Country          GDP 
_                  Japan            14 
_                  China            12 
_                  India             LW 
 
 
Summary Worksheet 
COL A          Country          GDP    Population 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've tried the code below, but this yields a 1004 run-time Error.


	VB:
	
 
Sub PasteIDS() 
     
    Dim lastRow As Range 
    Dim PasteSheet As String 
    Dim CopySheet1 As String 
    Dim CopySheet2 As String 
     
    PasteSheet = "Paste" 
    CopySheet1 = "Europe" 
    CopySheet1 = "Asia" 
     
    Set lastRow = Sheets(PasteSheet).Range("A65536").End(xlUp).Offset(2, 2) 
     
    Sheets(CopySheet1).Range(("B1"), Range("B65536").End(xlUp)).Copy lastRow.Offset(-1, 2) 
     
    Set lastRow = Sheets(PasteSheet).Range("A65536").End(xlUp).Offset(1, 2) 
     
    Sheets(CopySheet1).Range("C1", Range("C65536").End(xlUp)).Copy lastRow 
     
    Set lastRow = Sheets(PasteSheet).Range("A65536").End(xlUp).Offset(1, 2) 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks.

I have a spreadsheet with two Columns of data as below.I need to combine the two into one column so that I'd end up with one cell containing both sets of numbers. Thanks for the Help.

I now have two columns:
Column A Column B
30° 58' 31" 88° 8' 51"

Need to convert to this:
Column A
30° 58' 31" 88° 8' 51"

Dear Excel users

I would like to transfer my data from multiple columns to sinlge column.

The length of the columns is varies and it also contains gaps or some columns are empty.

I also attached a copy for my data for your reference.

Kindly adivce.

Many Thanks
Balaji

A B C D E
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

You have a data array of numbers appearing in columns A - E. The numbers appear in the order indicated above. The data array can vary from 1 to 7000 numbers depending on data downloaded to Excel. Therefore, the data will appear from Row 2 to possibly Row 1400. The goal is to place the numbers in order of appearance down a single column.
For instance if the results were to appear in column G the would appear in the following cell reference order:
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
A3
B3
C3
D3
E3
.
.
.

I have an array of data with column headings and changeable row "item names"
in the left most column. The intersecting matrix of data contains quantity
values at the intersection cells of column and row headings. I need to
automatically reorganize the data as a list in a column with the row "Item
Names" at the left but and repeated as necessary but instead of the
additional columns of quantity values, I need to have this data listed in a
single vertical column along with an additional column with the original
column header that corresponds to the quantity value. I think there must be
some kind of lookup function to do this but I can't find the right way to do
it. Anyone have any ideas about this?
Thanks,
RDW

Hi,

I'm posting this actually as an answer to the question I kept googling
and googling for but couldn't find a straightforward answer for. Maybe
others will reply with a better way to solve this problem.

Anyways, I have data in a single column in one Excel spreadsheet that
I want to extract, process in memory, and insert into another
spreadsheet.

here's the code I used

Dim idArray() As Variant
ReDim idArray(13 to srcRange.Rows.Count,1) 'note that this is a 2d
array even though I only need a single column.

For i = 13 to srcRange.Rows.Count
idArray(i,0) = srcRange.Cells(i,1).Value
Next

'Process array data
.....
'Put data back starting at second cell of first column
Worksheets(1).Range("A2").Resize(UBound(idArray)). Value = idArray

The part that kept vexing me was that idArray needs to be 2
dimensional even though I only need to copy a single column into the
array and back. Hopefully this will keep someone else from going batty
trying to figure this out!

Thanks,
Walter

I need help on a lookup and not sure which route to take. I have one cell on a worksheet that has no reference to it. I have a single column array that has a bunch of blank entries except for one value. The value fluctuates on a daily basis but is always the only value posted in the single column array. I need the cell on the initial workbook to find that value (its always a number greater than zero, the rest are blank) in the single column array. i will attempt to display how it is layed out below:

Day 1 Day 2
[blank cell] [blank cell]
[blank cell] [blank cell]
[blank cell] 5.250
5.000 [blank cell]
[blank cell] [blank cell]

Can someone help?

Hi
I currently have some code which is selecting 5 cells from a row where one of those cells is a specific number, and copying that data to a new sheet. The source file and cells contain some billing information and the output destination file is an invoice. My source file contains information for many customers, so my copy selection code copies data for each individual customer to a new sheet so I get an invoice for each customer. Here is my current code:

Set SrcRng =
Workbooks("SOURCE DATA.XLS").Worksheets("Specific Data").UsedRange
Set DstRng = Workbooks("INVOICES.xls").Worksheets("Customer #2").Range("A10")

    KeepColumns = Array("A", "B", "C", "E", "D")
    MatchColumn = "C"
    MatchValue = 2

      For Each Cell In SrcRng.Columns(MatchColumn).Cells
        If Cell.Value = MatchValue Then
           R = Cell.Row
           C = 0
           For Each Col In KeepColumns
             DstRng.Offset(N, C) = SrcRng.Cells(R, Col)
             C = C + 1
           Next Col
           N = N + 1
        End If
      Next Cell
Column E in my source data contains my employees hours. When I bill my customers and invoice them I bill 1.5 hours of travel time separately, so I need to deduct that time from my employees hours on the invoice.
Basically I need to subtract 1.5 from each cell in column E when it pastes to the new inovice workbook.

How can I accomplish this?

Any and all help is much appreciated.
Thanks!!!!

I have an array of data with column headings and changeable row "item names"
in the left most column. The intersecting matrix of data contains quantity
values at the intersection cells of column and row headings. I need to
automatically reorganize the data as a list in a column with the row "Item
Names" at the left but and repeated as necessary but instead of the
additional columns of quantity values, I need to have this data listed in a
single vertical column along with an additional column with the original
column header that corresponds to the quantity value. I think there must be
some kind of lookup function to do this but I can't find the right way to do
it. Anyone have any ideas about this?
Thanks,
RDW

Hi,

Is there a way to tell VBA to work with all the rows in a single column of a multi-dimensional array?

My problem is I have a multi-dimensional array that contains data that I want to do some calculations on. Let's say the array has 10 rows and 5 columns. If I wanted to take the average and standard deviation of each column individually, how would I do that?

What I had in mind was something like this...

For i = 1 to no_columns
avg_temp = application.worksheetfunction.average(array_values( ? , i)
stdev_temp = application.worksheetfunction.stdev(array_values( ? , i)
next i

I don't know how to tell VBA that I want to take into account all the rows from column i of the array. Any help would be greatly appreciated.

Thanks.

-HSM


No luck finding an answer? You could always try Google.