Reformatting data

[Viski]

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

i.e.

B1

45

B2

66

B3

60

B3

25

B4

45

B7

10

<tbody>
</tbody>

into

B1	45
B2	66
B3	60
B3	25
B4	45
B7	10

<colgroup><col><col></colgroup><tbody>
</tbody>

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...

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​

into

4​

B1

45

5​

B2

66

​

6​

B3

60

​

7​

B3

25

​

8​

B4

45

​

9​

B7

10

​

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...

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​

into

4​

B1

45

5​

B2

66

6​

B3

60

7​

B3

25

8​

B4

45

9​

B7

10

<tbody>
</tbody>

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