Given your example table, and assuming that the cell containing the "Year 1" header is A1, you can do this to get a column that lists "Year X" on each row.
Cell C2: =IF(LEFT(A1,4)="Year",A1,IF(LEFT(A2,4)="Year","",C1))
Breaking it down:
=IF(LEFT(A1,4)="Year",
- The first IF statement will be TRUE when the first four characters in A1 are "Year".
A1,
- When the IF statement is TRUE, C2's value will copy A1.
IF(LEFT(A2,4)="Year",
- When the first IF statement is FALSE, the second IF statement will check to see if A2 starts with "Year".
"",
- When the second IF statement is TRUE (and the first is FALSE), C2 will be blank.
C1))
- When both IF statements are FALSE, C2 will pull the value from C1.
For C2, the formula will return "Year 1". Copy the formula down the rest of the column, and the cell references will automatically adjust so that the "A1" references always point two to the left and one up from the current cell, and the "C1" reference always points to the cell immediately above the current cell. So, for your current list, column C will be added so the table looks like:
Year 1 Count Year
02-August 1 Year 1
05-August 1 Year 1
09-August 4 Year 1
Year 2
02-August 3 Year 2
To translate the years into regular year values, you'll need either a lookup table that defines which year ID matches to which calendar year, or you at least need to know what "Year 1" is. How you do it without a lookup table may be dependent on your formatting in column A, so I'll just
Using a lookup table in columns F:G (column F has "Year X", replacing X with actual numbers, column G has the corresponding four-digit years), here's how to put the year in D2: =IF(C2="","",VLOOKUP(C2,F:G,2,FALSE))
=IF(C2="","",
- If C2 is blank, leave D2 blank. Otherwise, the VLOOKUP would error on blanks.
VLOOKUP(C2,F:G,2,FALSE))
- When C2 isn't blank, look for the value of C2 in the left-most column of F:G, and return the corresponding value from the second column. Only use exact matching.
Without the lookup table, as long as the years are consecutively numbered, you could do this in D2: =IF(C2="","",RIGHT(A2,LEN(A2)-5)+2012-1)
=IF(C2="","",
- If C2 is blank, make D2 blank. Otherwise...
RIGHT(A2,LEN(A2)-5)
- Take all but the first 5 characters from A2. (This should be just the "X" part of "Year X".)
+2012
- Add 2012 (replace 2012 with the actual year you want for Year 1).
-1)
- Subtract 1 (since "Year X" starts from 1. Otherwise, all your year values will be one over, or you'll need to adjust "2012" (your "Year 1") to be "2011" (the year prior to "Year 1").
If you just want to tack the year onto the end of the date, and don't care about having dates formatted as actual dates, you could then do this:
Cell E2: =IF(D2="","",CONCATENATE(A2," ",D2))
IF(D2="","",
- If D2 is blank, leave E2 blank. Otherwise you'll have cells with "Year X" type values in them now and then.
CONCATENATE(A2," ",D2))
- If D2 isn't blank, E2 will have the value of A2, followed by a space, followed by the value of D2, combined into one string.
In the end, your sheet should look something like this (I added headers to C:G, and you can do without F:G entirely if you use the "without lookup table" method for D):