Converting different length rows into a single column

[carnamin]

I am a beginner user of VBA so I don't really know how to write a piece of code by myself... but I really need this in order to proceed with my research, which is not really related to computers; so if anyone could help me out with this I would save a lot of time.

I have a table in excel in which every line has as many values as the days of the month (e.g. 31 values first row, 28 the second..) and it goes from January 1948 to July 2014. So it ends up having around 800 rows.

I need to put all of those into a column, but it gets hard because the rows have different lengths. I have seen in this forum some solutions that apply well when the table is regular, but I'm stuck in trying to get a solution for when it's not. I have tried to record a macro using TRANSPOSE, but then I cannot change the cells into this formula in the editor in order to create a loop.

I also have to consider leap years.

I would really appreciate any help.

Thx to everyone!
Carlos.

 

[StephenCrump]

Welcome to MrExcel!

Perhaps something like this, without using VBA:

Excel 2010

	A	B	C	D	E	F	G	AE	AF	AG
As a single column

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]As date[/TD]
[TD="align: right"]As text (maybe)[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01 Jan 1948[/TD]
[TD="align: right"]January 1948[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]…..[/TD]
[TD="align: center"]ac[/TD]
[TD="align: center"]ad[/TD]
[TD="align: center"]ae[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01 Feb 1948[/TD]
[TD="align: right"]February 1948[/TD]
[TD="align: center"]p[/TD]
[TD="align: center"]q[/TD]
[TD="align: center"]r[/TD]
[TD="align: center"]s[/TD]
[TD="align: center"]..[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01 Mar 1948[/TD]
[TD="align: right"]March 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01 Apr 1948[/TD]
[TD="align: right"]April 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01 May 1948[/TD]
[TD="align: right"]May 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]01 Jun 1948[/TD]
[TD="align: right"]June 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]01 Jul 1948[/TD]
[TD="align: right"]July 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]01 Aug 1948[/TD]
[TD="align: right"]August 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]01 Sep 1948[/TD]
[TD="align: right"]September 1948[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]01 Jan 1948[/TD]
[TD="align: center"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]02 Jan 1948[/TD]
[TD="align: center"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]03 Jan 1948[/TD]
[TD="align: center"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]….[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]31 Jan 1948[/TD]
[TD="align: center"]ae[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]01 Feb 1948[/TD]
[TD="align: center"]p[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]02 Feb 1948[/TD]
[TD="align: center"]q[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Helper column for looking up dates:
A2: 1/1/1948 (as date)
A3: =EDATE(A2,1) copied down

Define range MyTable = C2:AG10, i.e. your whole calendar.

Calendar converted to a single column:
A13: 1/1/1948 (as date)
A14: =A13+1 Copied down
B13: =INDEX(MyTable,MATCH(A13,A$2:A$10,1),DAY(A13))

 

[carnamin]

Thank you very much for your answer!!

It worked!!

 

[StephenCrump]

Great! Thanks for the feedback.

 

[hiker95]

carnamin,

Welcome to the MrExcel forum.

You can do this with a macro using DataSeries.

Sample raw data (not all rows are shown for brevity):

Excel 2007
	A	B	C
1		1-Jan-48	30-Jul-14
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
60
61
24316
24317
24318
24319
Sheet1

Cell Formulas
Range	Formula
C1	=TODAY()

After the macro:

Excel 2007
	A	B	C
1	1-Jan-48	1-Jan-48	30-Jul-14
2	2-Jan-48
3	3-Jan-48
4	4-Jan-48
5	5-Jan-48
6	6-Jan-48
7	7-Jan-48
8	8-Jan-48
9	9-Jan-48
10	10-Jan-48
11	11-Jan-48
12	12-Jan-48
13	13-Jan-48
14	14-Jan-48
15	15-Jan-48
16	16-Jan-48
17	17-Jan-48
18	18-Jan-48
19	19-Jan-48
20	20-Jan-48
21	21-Jan-48
60	29-Feb-48
61	1-Mar-48
24316	28-Jul-14
24317	29-Jul-14
24318	30-Jul-14
24319
Sheet1

Cell Formulas
Range	Formula
C1	=TODAY()

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ExpandDateSeries()
' hiker95, 07/30/2014, ME794900
Dim MyStart As Long, MyStop As Long, n As Long
Columns(1).ClearContents
MyStart = Range("B1")
MyStop = Range("C1")
n = (MyStop - MyStart) + 1
Range("A1") = MyStart
With Range("A1:A" & n)
  .DataSeries Step:=1, Stop:=MyStop
  .NumberFormat = "d-mmm-yy"
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExpandDateSeries macro.