Formula to create a month range based on whether or not values are present in a range

[MSCHICK1]

I am having a devil of a time trying to figure this out --

I am trying to restructure a rather complicated Frankenstein's monster of a spreadsheet into a format that can be uploaded to a software system built to replace said Frankenstein's monster. A tiny bit of background: the spreadsheet tracks resource allocations to projects for a department in my company. The new software needs each allocation to have a start date and end date, but the data in the spreadsheet is listed per month - an allocation percentage is either present or not for each month. Because of the volume of projects (there are 75) and resources (there are 204), determining the start and end dates manually would be very time consuming.

snippet.png

I am attaching an image snippet of the Frankenstein's monster spreadsheet so that you can get a sense of the current format of the data. What I need is to figure out what sort of formula I could insert in the Duration row (highlighted) for each project to look at the 12 rows below it, and return the corresponding month value for both the first in the series with a value in it, and the last.

Is this possible, or am I doomed to do this manually?

[fshbet]

Hi,

Please find attached for your solution.

Add your newly created projects in ProjectID sheet and run the macro.

[MSCHICK1]

Ravikumar,

When I run this macro in my spreadsheet, all it seems to do is copy down highlighted areas thousands of times. Thanks for trying, though!

[Glenn Kennedy]

Row 41. What is your expected answer?

[MSCHICK1]

Row 41 should be as follows, summarizing the twelve rows below it:

50746

Duration

Apr - Jul

Mar - Aug

Mar - Aug

Jul - Dec

Jul - Dec

[Glenn Kennedy]

OK. In C2:
=IFERROR(INDEX($B3:$B14,MATCH(TRUE,INDEX(ISNUMBER(C3:C14),),))&" - "&LOOKUP(2,1/(C3:C14<>""),$B3:$B14),"")

Copy across. Copy C2: S2 and copy into other rows....

[MSCHICK1]

YOU'VE DONE IT.

There is absolutely no way I would have ever been able to do that. THANK YOU SO MUCH. Really, this saves me weeks' worth of work.

Cheers!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.