Reformatting data

[Viski]

Hi all, I need to transpose my horizontal data to a vertical format:

i.e.
[TABLE="width: 258"]
<tbody>[TR]
[TD="width: 22"]B1[/TD]
[TD="width: 21, align: right"]45 [/TD]
[TD="width: 22"]B2[/TD]
[TD="width: 21, align: right"]66[/TD]
[TD="width: 22"] B3[/TD]
[TD="width: 21, align: right"]60 [/TD]
[TD="width: 22"]B3[/TD]
[TD="width: 21, align: right"]25 [/TD]
[TD="width: 22"]B4[/TD]
[TD="width: 21, align: right"]45[/TD]
[TD="width: 22"] B7[/TD]
[TD="width: 21, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

into

[TABLE="width: 43"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]B1[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

Normal transpose function won't work (it places the numbers on the next row) and have looked at =INDIRECT and =INDEX but haven't been able to sort it.

Any ideas? Thanks.

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]B1[/td][td]

45​

[/td][td]B2[/td][td]

66​

[/td][td]B3[/td][td]

60​

[/td][td]B3[/td][td]

25​

[/td][td]B4[/td][td]

45​

[/td][td]B7[/td][td]

10​

[/td][/tr]

[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]into[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]B1[/td][td]45[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]B2[/td][td]66[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]B3[/td][td]60[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]B3[/td][td]25[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]B4[/td][td]45[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]B7[/td][td]10[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][/tr]
[/table]
A4=INDEX(A$1:L$1,,ROWS($A$1:A1)*2-1)
copied down and across
(note this will only work for 2-entry "sets")

 

[JoeMo]

Here's a macro solution you can try. First select the data (A1:L1), then run the macro. Reformatted data are in A3:B8.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	B1	45	B2	66	B3	60	B3	25	B4	45	B7	10
2
3	B1	45
4	B2	66
5	B3	60
6	B3	25
7	B4	45
8	B7	10
Sheet2

Sub ViskiReformat()
'first select the data to be reformatted, then run this macro
Dim R As Range, Vin As Variant, Vout As Variant
Set R = Selection  'assumed to be a contiguous cells in a single row
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 2), 1 To 2)
For i = 1 To UBound(Vin, 2)
    If i Mod 2 <> 0 Then
        Vout(i, 1) = Vin(1, i)
    Else
        Vout(i - 1, 2) = Vin(1, i)
    End If
Next i
Application.ScreenUpdating = False
'reformat 2 rows below selection, in first 2 columns of selection
With R(1).Offset(2, 0).Resize(UBound(Vout, 1), 2)
    .Value = Vout
    .SpecialCells(xlCellTypeBlanks).Delete
End With
Application.ScreenUpdating = True
End Sub

 

[Viski]

Try this...
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]B1[/TD]
[TD]

45​

[/TD]
[TD]B2[/TD]
[TD]

66​

[/TD]
[TD]B3[/TD]
[TD]

60​

[/TD]
[TD]B3[/TD]
[TD]

25​

[/TD]
[TD]B4[/TD]
[TD]

45​

[/TD]
[TD]B7[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]into[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]B1[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]B2[/TD]
[TD]66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]B3[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]B3[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]B4[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]B7[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A4=INDEX(A$1:L$1,,ROWS($A$1:A1)*2-1)
copied down and across
(note this will only work for 2-entry "sets")

I'm not sure how you reply here but thanks to both of you - i never would've worked that out in a million years!!

 

[Viski]

Thanks Joe - legend.

 

[Viski]

Thanks FD - worked perfectly.

 

[FDibbins]

Great, happy to help