Appending/merging rows horizontally

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

miaparisian@gmail.com - 30 Oct 2006 21:35 GMT

Hi, I have data for several human subjects and their data is set up in
an excel file so that all the data for person 1 takes up 15 rows,
person 2 the next 15 rows, etc. I would like to make all of a single
person's data merged into one row horizontally, so that each human
subject has one row of data.

For example:
----------------------------------
001
a b c d e f g h
i j k l m
n o p q r s
t u v w x
y z
002
a b c d e f g h
i j k l m
...
----------------------------------
001 is the human subject number and each letter (datum) is in their own
cell. I would like it to read:

001 a b c d e f g h i j k l m n o p q r s t u v w x y z
002 a b c d ....

Is there a way I can do this keeping each letter in its own cell. I'm
not too experienced with Excel, so any step-by-step help would be
greatly appreciated.
Thanks

Reply to this Message

Gord Dibben - 30 Oct 2006 22:13 GMT

Assuming data is in column A.......

In B1 enter this formula

=INDEX($A:$A,(ROWS($1:1)-1)*15+COLUMNS($A:B)-1)

Drag/copy across to P1

Select B1:P1 and drag/copy down until you get zeros.

When happy, copy and paste special(in place)>values>OK>Esc

Delete column A

A macro can go much faster and leave no formulas to deal with.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub

You would enter 15 in the inputbox.

Gord Dibben MS Excel MVP

>Hi, I have data for several human subjects and their data is set up in
>an excel file so that all the data for person 1 takes up 15 rows,
[quoted text clipped - 25 lines]
>greatly appreciated.
>Thanks

Reply to this Message