Match/Index first and last populated cell to corresponding column header (Gantt chart formulas)

[Mrgoodkat]

Hi All,

I am dealing with a document similar to a very large Gantt chart with Dates at the top of each column and non zero values in the grid below recording which projects are worked on.

I am looking for a formula that can read the corresponding date to the first and last entry within each row: thus producing a start and end date on each project. However, Zeros populate the 'empty' cells and this cannot be avoided as the data is copied and pasted by multiple other people, and so I am looking for two formulas that can take this into account.

[TABLE="width: 500"]
<tbody>[TR]
[TD](A1)[/TD]
[TD]09/09/2014[/TD]
[TD]10/09/2014[/TD]
[TD]11/09/2014[/TD]
[TD]12/09/2014[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]0[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]10/09/2014[/TD]
[TD]11/09/2014[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]09/09/2014[/TD]
[TD]10/09/2014[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]12/09/2014[/TD]
[TD]12/09/2014[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]0[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]10/09/2014[/TD]
[TD]11/09/2014[/TD]
[/TR]
</tbody>[/TABLE]

I have been using the following formulas: (In this example for Project 1)

Start date: {=IF(ISERROR(INDEX(B$1:E$1,MATCH(FALSE,ISBLANK(B2:E2),0))),0,(((INDEX(B$1:E$1,MATCH(FALSE,ISBLANK(B2:E2),0))))))}

End Date: {=IF(ISERROR((LOOKUP(2,1/(1-ISBLANK(B2:E2)),B$1:E$1))),0,(LOOKUP(2,1/(1-ISBLANK(B2:E2)),B$1:E$1))}

This formula does work if the empty cells are actually empty i.e do not contain 0's but as said previously this cannot be avoided and so I am looking for a formula that can ignore 0 values.

 

[Momentman]

Do you actually have "X" in those cells or those are used to represent actual numbers in there?

 

[Momentman]

Do you need something like this

Excel 2010
	A	B	C	D	E	F	G	H
1	(A1)	09/09/2014	10/09/2014	11/09/2014	12/09/2014		Start Date	End Date
2	Project 1	0	10	20	0		10/09/2014	11/09/2014
3	Project 2	30	40	0	0		09/09/2014	10/09/2014
4	Project 3	0	0	0	15		12/09/2014	12/09/2014
5	Project 4	0	33	17	0		10/09/2014	11/09/2014
Sheet4

Cell Formulas
Range	Formula
G2	{=INDEX($B$1:E2,1,MATCH(1,IF(B2:E2<>0,1/(B2:E2<>0)),0))}
H2	{=LOOKUP(9.99E+307,IF(B2:E2<>0,B2:E2),$B$1:$E$1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mrgoodkat]

Hi Momentman,

Thanks for your swift reply, apologies I made such a rushed post, but your assumptions were absolutely right!

Your spreadsheet mock up is also a much better explanation for others wanting a solution to this - and I can confirm that it works flawlessly.

Thanks again for your help!

 

[Momentman]

You are welcome. Glad to help