Filling in Ascending Values between Two Interval Columns

[afufoo]

I'm a bit new to this, and have been reading post after posts with no success. I have a time line between two dates (years) and I need to find a way to fill in missing years between the two. i.e. 1994 - 2000; There are 6 years missing in between from 1995-1999 which I need to have filled into individuals columns in between, the beginning and end year columns (8 column width total). I have attached the spreadsheet to which I'm referring to, please if ANYONE knows a formula or steps on how to do this, I'd be so grateful. I'm so burnt out from trying to read and find it on this forum from past posts. And about to grow some gray hairs and pull them out! FILL-IN INTERVAL.xlsx

[TMS]

One way:

In D8: =$A8

In E8: =IF(D8="","",IF(N(D8)+1>$B8,"",N(D8)+1)) and drag across for as many years as you are likely to have a gap.

Then select all the formulae on the row and drag down to the end of the column of data.


Regards, TMS

[afufoo]

oh my lord thank u so much!!

[TMS]

You've posted in the Excel Formulas & Functions forum ... so you got a formula solution


Do you want a VBA solution? Not everyone does.


I'm not sure of the relevance of the number of rows. If you put the formulae into the first row, select all the cells with formula on the row, then put the cursor over the bottom right corner so it turns into a fine cross hair and double click, it should autofill down to the bottom.

Note that the formula doesn't cater for blank rows or rows with "ALL" in cells in column A


Regards, TMS

[afufoo]

Thanks so much TMShucks, how would I also be able to concatenate all these years into one cell, with commas separating years. However, how do I set a perfect amount of "," in between each year so that there wont be extra common left over on some of the shorter year intervals?

[TMS]

You're welcome. Thanks for the rep.


Editing your post #2 puts my reply out of context but not to worry.


Regards, TMS

[TMS]

I think you'd need a User Defined Function (UDF) for that.

Have a look at CONCATALL from TigerAvatar.

http://www.excelforum.com/tips-and-t...geravatar.html


Regards, TMS

[afufoo]

Thank you for replying TMShucks! I tried adding the code into Modules in Visual Basics, but when I go back to the spreadsheet it labels my =ConcatAll cell as #NAME? I have attached a file to show both example, and the problem. Thanks!

[afufoo]

anyone ? thank u so muchhh if anything!

[TMS]

A workbook with code needs to be saved as an .xlsm macro enabled workbook.

See the attached. It has TA's UDF and something I just knocked up for your needs. Unless you actually need the years listed in separate cells, you can probab;y use this instead of my formulae and TA's UDF.


Regards, TMS

[afufoo]

okay TMShucks u are officially genius. thanks so much, everything I needed was solved. I re-saved in Macro format also, and looked over the two modules you have created in it. The ConcatYears is actually a great one to use, although TA's code is more universal. However for this project, I prefer the ConcatYears, thanks again!! is there a link where I can give positive reviews to helpers (aka YOU)?

[TMS]

For thanking people ...

http://www.excelforum.com/the-water-...-the-help.html

With your data as originally presented, and with my formula to calculate the years, TA's UDF is ideal and, as you say, very generic. I just put the new UDF together as it may be that you only went down that track in the absence of what you actually needed.


Regards, TMS