Multiple columns into 0ne

P

Prixton

Hi,

We get a lot of information that comes into different columns, say Product
1 - 88. We never know how many values there are in every colum or how many
Products there are

Now we want a macro that puts all the values into one column, say A - can be
in sheet 2. We do not want empty rows or columns but it is very important
that the figures are in the original order.

Regards
 
J

Jerry W. Lewis

More information is needed. Is there a header row? If so, what do you
want to become of it? Might the be empty cells within the table? How
do you recognize how many columns are used, or is the first row always
filled for each column? If one of the products uses 17 rows, will all
of the products use 17 rows? What do you mean by "the original order"
when you are combining multiple columns into one colum; would
a b c
d e f
g h i

become
a
b
c
d
...
or
a
d
g
b
...

Jerry
 
G

Gary''s Student

1. enter the following small udf:

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function

2. click on column A and Insert > Column this will result in a new un-used
column
3. click on the new A1 and enter =concat(B1:IV1)
4. copy down as far as necessary


finally locate and delete any empty rows
 
P

Prixton

Hi,
Yes there is a header row but we are not interrested in getting the header
into the new column.
All posts from col A should be followed by B, C, D etc. The columns have
different number of values, there are no empty cells within a column .
We want the result to be
a
b
c
d
e
f
g

Regards
 
Top