I am trying to create a range from numbers in a column, and create a range. For example,
column F:
1899
1912
1915
1918
1929
1934
1935
1936
...
So as soon as 1934, 1935, 1936 sequence starts, it would capture 1934 and iterate through until the difference isn't 1.
The result for this example would be:
1934 - 1936.
Once I create these date ranges, I would use text to columns by the "-" delimiter to create begin and end date columns.
The difficulty in this is comparing another column and making sure the dates are based on a particular title.
So: IF(Title 1 = Title 2, (IF date 2 - date 1 = 1, increment to check date 3 - date 2 = 1, otherwise = date 1)).
I could try to nest many IF statements, but that would get nasty, plus no idea how many dates to compare per title.
Iteration seems like it would be much easier by creating a sub or UDF in VBA, but I haven't found any examples on here (or elsewhere) that may be helpful.
This will do the job, it will take cells in column F and print your output in column G. If there is a range of years (ex: 1934-1935-1936), it will put it in a single cell as a string.
Sub range_dates()
Dim cel As Range
Dim iRow As Long, n As Long
Dim title As String, nextTitle As String
iRow = 2
n = 0
For i = 2 To 15
Set cel = Cells(i, "F")
title = Cells(i, "E").Value
nextTitle = Cells(i + 1, "E").Value
diff = Cells(i + 1, "F").Value - cel.Value
If diff = 1 And title = nextTitle Then
firstDate = cel.Value - n
n = n + 1
Else
Cells(iRow, "H").Value = cel.Value
Cells(iRow, "G").Value = title
If n > 0 Then
Cells(iRow, "H") = "'" & firstDate & " - " & cel.Value
n = 0
End If
iRow = iRow + 1
End If
Next
End Sub
I do know this code is not the most optimized, but as of now it's the one i have to share. Feel free to improve it :)
EDIT: I added the title conditions and changed the output to prevent excel from changing the string to a date format
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments