Bring values in a column into a row

[peterh]

I have a speadsheet with:-

1
23
4
5


34
4
3
5
6
2


2
3
4
3
2

I need the above info displayed
1 23 4 5
34 4 3 5 6 2
2 3 4 3 2

 

[Jacob Skaria]

One way is to use a macro.. You can try out the below macro. If you are new
to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub MyMacro()
Dim lngRow As Long, lngNRow As Long, lngNCol As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Trim(Range("A" & lngRow)) <> "" Then
lngNRow = lngNRow + 1
lngNCol = 1
Do While Trim(Range("A" & lngRow)) <> ""
lngNCol = lngNCol + 1
Cells(lngNRow, lngNCol) = Range("A" & lngRow)
lngRow = lngRow + 1
Loop
End If
Next
End Sub

If this post helps click Yes

 

[Ashish Mathur]

Hi,

Suppose the data below is in range B5:B26. In A5, enter 1, in cell A6,
etner =IF(AND(B5="",B6<>""),MAX($A$5:A5)+1,IF(B6="","",A5)) and copy till
A26. In F5:F7, enter 1,2,3. In cell G5, enter the following array formula
(Ctrl+Shift+Enter) and copy down and across

=IF(ISERROR(INDEX($A$5:$B$26,SMALL(IF($A$4:$A$26=$F5,ROW($A$4:$A$26)-ROW($A$4)),COUNTA($F5:F5)),2)),"",INDEX($A$5:$B$26,SMALL(IF($A$4:$A$26=$F5,ROW($A$4:$A$26)-ROW($A$4)),COUNTA($F5:F5)),2))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[peterh]

Jacob, Thanks, I personaly didn't under stand, (don't do Macro's) but the
boss did & said it may help. Thanks again.

 

[peterh]

Jacob,

There seems to be a bug in this row
Do While Trim(Range("A" & lngRow)) <> ""
and the compiler stops?

Please advise - Thanks Peter

 

[Jacob Skaria]

I have tried this with several samples; but unable to recreate the error you
mentioned.

--Just copy the data to Column A
--The macro will transpose data in sets in Column B

1
23
4
5


34
4
3
5
6
2


2
3
4
3
2

3

32

'Transposed to
1 23 4 5
34 4 3 5 6 2
2 3 4 3 2
3
32


If this post helps click Yes