Vertical to horizontal

[Zubair]

Hi Experts,

A very long data is available in column A, I want the formula to bring data horizontally from column b to j, please help

	Date	Cost 1	Cost 2	Cost 3	Cost 4	Cost 5	Cost 6	Cost 7	Cost 8
	Sunday, April 16, 2006	28.5​	0.88​	13.86​	2.3​	1.59​	1.82​	7.34​	56.29​
	Monday, May 01, 2006	29.89​	0.88​	12.72​	3.18​	1.63​	1.87​	7.53​	57.7​
Sunday, April 16, 2006
28.5​
0.88​
13.86​
2.3​
1.59​
1.82​
7.34​
56.29​
Monday, May 01, 2006
29.89​
0.88​
12.72​
3.18​
1.63​
1.87​
7.53​
57.7​
Tuesday, May 16, 2006
30.31​
0.88​
12.3​
3.18​
1.63​
1.87​
7.53​
57.7​

 

[Peter_SSs]

Are those dates in column A text values or are they actual dates (numbers) formatted to show day, month etc?

 

[Peter_SSs]

Actually, might not need that information since your data seems very regular.
Test this with a copy of your data.
Assumes that first date is in A4

Sub Vert_Horiz_1()
  Dim i As Long
 
  Application.ScreenUpdating = False
  For i = 4 To Range("A" & Rows.Count).End(xlUp).Row Step 9
    Range("A" & i).Resize(9).Copy
    Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Next i
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[AlanY]

try this

Rename v3.03.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Cost 1	Cost 2	Cost 3	Cost 4	Cost 5	Cost 6	Cost 7	Cost 8
2		Sunday, April 16, 2006	28.5	0.88	13.86	2.3	1.59	1.82	7.34	56.29
3		Monday, May 01, 2006	29.89	0.88	12.72	3.18	1.63	1.87	7.53	57.7
4	Sunday, April 16, 2006
5	28.5
6	0.88
7	13.86
8	2.3
9	1.59
10	1.82
11	7.34
12	56.29
13	Monday, May 01, 2006
14	29.89
15	0.88
16	12.72
17	3.18
18	1.63
19	1.87
20	7.53
21	57.7
22	Tuesday, May 16, 2006
23	30.31
24	0.88
25	12.3
26	3.18
27	1.63
28	1.87
29	7.53
Sheet3

Cell Formulas
Range	Formula
C2:J3	C2	=TRANSPOSE(OFFSET($A$1,MATCH(B2,A:A,0),0,8))
Dynamic array formulas.

 

[Zubair]

Actually, might not need that information since your data seems very regular.
Test this with a copy of your data.
Assumes that first date is in A4

Sub Vert_Horiz_1()
  Dim i As Long
 
  Application.ScreenUpdating = False
  For i = 4 To Range("A" & Rows.Count).End(xlUp).Row Step 9
    Range("A" & i).Resize(9).Copy
    Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Next i
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Thanks, Peter it's perfectly working

 

[Peter_SSs]

You're welcome.

But in future, if you actually want a vba solution please say so, and not ..

I want the formula to ...