find the first non blank cell in every 3rd column

[hobgoblinvasya]

hi all,

here is a hard one for all you gurus

i need to find the first non blank cell, checking cells in the same row in every third column to the left of the original cell, up until a certain column.

so lets say i have a cell "AB1". I need to find the first non empty cell to the left of AB1 that is 3,6,9... etc columns from it. So lets say either Y1, V1 or S1. Then if that cell contains "Yes", i need to get the value of that cell. So if Y1 is non empty, i check if Y1 = "Yes", i need to use Y1, if not then i put an empty value. If Y1 was empty, i need to check for V1, then for S1, etc. This should accommodate more then 8 conditions, thus i would like to avoid nested IFs.

so, something like this, but without IFs:
=IF(TRIM(Y1)<>"",IF(Y1<>"Yes",Y1,""),IF(TRIM(V1)<>"",IF(V1<>"Yes",V1,""))

Thanks in advance!

 

[VoG]

Try in AB1

=LOOKUP(REPT("z",255),A1:AA1)

Ignore - missed the 3 columns bit.

 

[MrKowz]

Perhaps:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">#N/A</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">AB2</th><td style="text-align:left">{=INDEX(<font color="Blue">A2:AA2,MATCH(<font color="Red">9.99E+307,IF(<font color="Green">MOD(<font color="Purple">COLUMN(<font color="Teal">A2:AA2</font>),3</font>)=1,IF(<font color="Purple">A2:AA2="Yes",1,""</font>)</font>)</font>)</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 />

 

[hobgoblinvasya]

@VOG

first off, thank you !

now on to the formula, hm... could you explain it? from what i can see this finds the first non empty cell. However when i put it in a cell, all i get is an empty result.

also, i need to check for every 3rd cell to the left, not every cells. any idea on how to achieve that, as i am absolutely buffled.

 

[hobgoblinvasya]

Perhaps:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">#N/A</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">AB2</th><td style="text-align:left">{=INDEX(<font color="Blue">A2:AA2,MATCH(<font color="Red">9.99E+307,IF(<font color="Green">MOD(<font color="Purple">COLUMN(<font color="Teal">A2:AA2</font>),3</font>)=1,IF(<font color="Purple">A2:AA2="Yes",1,""</font>)</font>)</font>)</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 />

WOW!!! This almost does the trick. Is there any way to do this lookup in reverse? From AA to A?

 

[MrKowz]

also, i need to check for every 3rd cell to the left, not every cells. any idea on how to achieve that, as i am absolutely buffled.

This code will do the same as what VoG posted, as well as check only every 3rd cell to the left of where the formula is entered.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>No</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>No</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>No</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">#N/A</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet3</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AB1</TH><TD style="TEXT-ALIGN: left">{=INDEX(A1:AA1,MATCH(9.99E+307,IF(MOD(COLUMN(A1:AA1),3)=MOD(COLUMN(),3),IF(A1:AA1="Yes",1,""))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

WOW!!! This almost does the trick. Is there any way to do this lookup in reverse? From AA to A?

It does. ^_^

 

[hobgoblinvasya]

Got it! i will simply do this :
=INDEX(A2:AA2,COLUMN (AA1)-MATCH(9.99E+307,IF(MOD(COLUMN(A2:AA2),3)=1,IF(A2:AA2="Yes",1,""))))

EDIT: Nope, that doesnt work

 

[hobgoblinvasya]

WOW! You are a genius MrKowz! My eternal Gratitude to you !

 

[MrKowz]

WOW! You are a genius MrKowz! My eternal Gratitude to you !

Glad it works, thanks for the feedback!