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

Mrgoodkat

New Member
Joined
Aug 20, 2014
Messages
5
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.

(A1)09/09/201410/09/201411/09/201412/09/2014Start DateEnd Date
Project 10xx010/09/201411/09/2014
Project 2xx0009/09/201410/09/2014
Project 3000x12/09/201412/09/2014
Project 40xx010/09/201411/09/2014

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Do you actually have "X" in those cells or those are used to represent actual numbers in there?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Do you need something like this

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">(A1)</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">09/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">11/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">12/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">Start Date</td><td style="color: #333333;background-color: #FAFAFA;;">End Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;background-color: #FAFAFA;;">Project 1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">20</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">11/09/2014</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;background-color: #FAFAFA;;">Project 2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">30</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">40</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">09/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10/09/2014</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;background-color: #FAFAFA;;">Project 3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">15</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">12/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">12/09/2014</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">Project 4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">33</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">17</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10/09/2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">11/09/2014</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$1:E2,1,MATCH(<font color="Red">1,IF(<font color="Green">B2:E2<>0,1/(<font color="Purple">B2:E2<>0</font>)</font>),0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99E+307,IF(<font color="Red">B2:E2<>0,B2:E2</font>),$B$1:$E$1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Mrgoodkat

New Member
Joined
Aug 20, 2014
Messages
5
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

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Glad to help
 

Watch MrExcel Video

Forum statistics

Threads
1,109,465
Messages
5,528,962
Members
409,848
Latest member
Blomsten
Top