Concatenating columns of different length

[DavidObeid]

Hi,

If I have a number of columns of numerical data that will be of
different lengths, is there an easy, non-VBA way to concatenate the
columns into one single column with no blank rows?

The original columns may have some blank cells.

Regards,

Dave

 

[Alan]

I may be missing something here, but with the data in cloumns A: F, in
cloumn G

=A1&B1&C1&D1&E1&F1
or
=CONCATENATE(A1,B1,C1,D1,E1,F1)
will do it, blank cells will not show in the result.

Hi,

If I have a number of columns of numerical data that will be of
different lengths, is there an easy, non-VBA way to concatenate the
columns into one single column with no blank rows?

The original columns may have some blank cells.

Regards,

Dave

creating financial statements

 

[DavidObeid]

Thanks Alan, but I don't think I explained myself right the first time.
Let me try again.

Column A has:

2
4
6

8
9
3
5


Column B has

4
6
2
1
0
7
6
1


How do I get column C to form these into one continuous list so tha
column C will have:

2
4
6
8
9
3
5
4
6
2
1
0
7
6
1

?

Regards,

Dav

 

[David McRitchie]

Hi Dave,
The following will create a new worksheet that is unsnaked by
column into Column A of the new worksheet..

Sub unsnake_bycol()
'David McRitchie, 2003-12-05 misc
'create new worksheet before current sheet
Dim wsSource As Worksheet, wsNew As Worksheet, c As Range
Dim cell As Range, rCnt As Long
Set wsSource = ActiveSheet
Set wsNew = Worksheets.Add
wsNew.Name = wsSource.Name & "_u"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
rCnt = 0
For Each c In wsSource.UsedRange.Columns
On Error Resume Next
For Each cell In c.SpecialCells(xlConstants, xlTextValues)
If Err.number = 0 Then
rCnt = rCnt + 1
wsNew.Cells(rCnt, 1) = cell.Value
End If
Next cell
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

 

[DavidObeid]

Thanks Dave (love your website btw), but that solution still leaves m
at a bit of a loss. Perhaps I over simplified my problem when I state
it.

I am putting together a spread sheet that a group of teachers will us
to generate statistical graphs and tables for students from differen
classes doing the same course.

All teachers will have uniformly formatted workbooks.

JohnSmith.xls and FredBloggs.xls will both have a sheet called "Year 1
General" in them (not necessarily in the same order within th
workbooks - Fred's Year 12 General might be his 2nd worksheet, wher
John's Year 12 General may be his 3rd worksheet (say)). There may b
upto 5 different teachers teaching different classes within the sam
course (eg Year 12 General), so there may (or may not be) present
JohnDoe.xls, JaneDoe.xls and JohnCitizen.xls present with a sheet name
"Year 12 General". All workbooks will be in the same directory.

Now, all teachers will enter their results for a given task (task 1'
results will be in column J, task 2 in column N... there may be upto
tasks, all 5 columns apart) in their workbook.

Sometimes students will be absent or/and have left during the semeste
and have no result recorded against their name for a given task (henc
the possibility of gaps in some of the columns).

What I need is a way of reading the relevant columns from the differen
workbooks and concatenating the contents so an annonymous "spot th
dot" (a sort of histogram that my co-ordinator likes to use) can b
generated. I need the results to be reported into column A (startin
at A15) of a sheet previously prepared called "spot the dot" in
workbook called "Automaticspotthedot.xls".

(How) can this be done?

Regards,

Dav