Extract records where adjacent cells meet 1 criteria

[Akashwani]

Good day.

I'm going round and round with this one, I'm sure I have the solution somewhere in my records, but alas, I have to post this problem to get the solution.

I need to return the values in Column A IF any of the adjacent cells (columns) contain a number.

Sample data and expected Results...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Data1	Data2		Data3	Data4		Data5		Result
2	01/01/2013	2								01/01/2013
3	02/01/2013	1	1							02/01/2013
4	03/01/2013		1							03/01/2013
5	04/01/2013				1	3				04/01/2013
6	05/01/2013									06/01/2013
7	06/01/2013					1				08/01/2013
8	07/01/2013									11/01/2013
9	08/01/2013					1				12/01/2013
10	09/01/2013									13/01/2013
11	10/01/2013									15/01/2013
12	11/01/2013							2
13	12/01/2013							1
14	13/01/2013		1		1
15	14/01/2013
16	15/01/2013				3
17
Sheet2

I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.

I look forward to the solution and I hope it isn't too obvious!

Thanks

Ak

 

[oldbrewer]

why does row 5 return the value in A5 - only data3 and data4 contain numbers.

 

[Aladin Akyurek]

I2, just enter and copy down:

=(COUNT(B2:C2,E2:F2,H2)>0)+0

J2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$16,SMALL(IF($I$2:$I$16=1,
  ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($J$2:J2))),"")

For a 2003 system or earlier:

=IF(ROWS($J$2:J2)<=SUM($I$2:$I$16),INDEX($A$2:$A$16,SMALL(IF($I$2:$I$16=1,
  ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($J$2:J2))),"")

 

[Akashwani]

Hi Aladin,

Thank you very much for your solution, I didn't think this would be possible without a "Helper" column.

I was using this as a "Helper" in Column I....
=IF(SUM(IF(ISNUMBER(B2:H2),1,""))>0,1,"")
But yours is just so much better.

I was really struggling to return the Results, I tried....
INDEX($A$2:$A$16,SMALL(IF($I$2:$I$16=1,ROW($A$2:$A$16)),ROWS(J$2:J2))-ROW($A$2)+1)
But obviously I was way off the mark!

Thanks again for your solution, greatly appreciated as always.

Ak

 

[Aladin Akyurek]

Hi Aladin,

Thank you very much for your solution, I didn't think this would be possible without a "Helper" column.
...

Thanks. Even if possible to do it without a 'helper' range, the set up would be expensive.

 

[aaalviny]

Hi,

I2
=IF(COUNT($B2:$C2, $E2:$F2, $H2)>0, 1, 0)

I3 Formula and downward
=IF(COUNT($B2:$C2,$E2:$F2,$H2)>0, I2+1, I2)

J2 Formula
=INDEX($A$2:$A$16, MATCH(1, $I$2:$I$16, 0))

J3 formula and downward
=INDEX($A$2:$A$16, MATCH(INDEX($I$2:$I$16, MATCH(J2, $A$2:$A:$16, 0))+1, $I$2:$I16, 0))

Please highlight cells I2 and J2 as their formulas are different to that below

I would like to address on the following issues
1.please try to have the same type of source data in a table without empty columns
2.try your best to alter the workbook design so that you can add columns freely for working (I know there must be limitations, just try to figure out the feasibility) you can have much shorter and easier-to-read and manage formulas

Alvin

 

[aaalviny]

Hi, sorry please find amendments (typo) below

Hi,

I2
=IF(COUNT($B2:$C2, $E2:$F2, $H2)>0, 1, 0)

I3 Formula and downward
=IF(COUNT($B3:$C3, $E3:$F3, $H3)>0, I2+1, I2)

J2 Formula
=INDEX($A$2:$A$16, MATCH(1, $I$2:$I$16, 0))

J3 formula and downward
=INDEX($A$2:$A$16, MATCH(INDEX($I$2:$I$16, MATCH(J2, $A$2:$A$16, 0))+1, $I$2:$I16, 0))

Please highlight cells I2 and J2 as their formulas are different to that below

I would like to address on the following issues
1.please try to have the same type of source data in a table without empty columns
2.try your best to alter the workbook design so that you can add columns freely for working (I know there must be limitations, just try to figure out the feasibility) you can have much shorter and easier-to-read and manage formulas

Alvin

 

[Akashwani]

Hi Alvin,

Thanks for your reply, I didn't receive a notification of your post!!

Ak