Condensing multiple rows of a column into the blank cell above them

[FreddyK]

Data coming from a pivot table is being copied to a new page, my overall goal is to condense the years as follows into a single cell. There can be anywhere from 1-15+ years before the next blank cell and they are not always consecutive years. Editing the same column is prefered but if it is easier in a new column that is fine also.
Cells are in General Format NOT Date (if that matters)

So basicly:
In a given range, (e.g. column in table, D2:D583)
Is cell blank?
n=count(cells between current blank and next blank)
if n=1, cell=R[1]C
if n>1, cell=R[1]C&"-"&R[n]C
if n=0, end (would have ran off of table at this point)
Year.png
Deleting the individual years is not required, they can remain in the column as the entire row gets deleted in a later step of my macro

[Bernie Deitrick]

Shouldn't the last one be NA-1986?

Anyway, try this - if you need to exclude NA from the list if there are year values that are valid, then it obviously needs to change to account for that.

Please Login or Register  to view this content.

[FreddyK]

Yes I was trying to exclude NA from appearing in multiple years, but out of 30+ documents (around 4k rows of item info ea) I have only seen this once. Thank you very much for the code, it seems to be working smoothly! What is the purpose of the ' out front?

Edit: I removed the "'" & and it seems to be running just the same

[Bernie Deitrick]

Sometimes entering a dash between two values results in a date and not a string - Excel does some checks on entry that can result in values you do not want. So the single quote keeps it a string. I could have formatted the cell as text, but that takes more typing If you always have 4 digit years, then it won't matter.

[FreddyK]

Ahh I assumed it had something to do with preventing the cells from auto formatting, thank you for your fast replies I am new to macros and I never would have figured that out on my own :p Whoever made the original data did not know that if you did =YEAR(BlankCell) it returned 1900, I was repalcing anything under 1910 with NA, just changed it to "" in the original sheet and then fill in the blanks at the end of my macro with NA so they were no longer an issue.

[Bernie Deitrick]

Do you need to modify the macro for the new values?

Also

=YEAR(BlankCell)

should be this to deal with possible blanks

=IF(PossBlankCell = "", "",YEAR(PossBlankCell))

[FreddyK]

I used

=IF(YEAR(N2)<1910,"",YEAR(N2))

I am pulling these files from our companies online database. Whoever made the original spreadsheets ages ago used =Year() then all cells were changed to values on conversion. Somehow he also has a few 1905 in there, not sure why. Anyways everything seems to be working now my 210 line code is complete