Extract records where adjacent cells meet 1 criteria

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
ABCDEFGHIJK
1DateData1Data2Data3Data4Data5Result
201/01/2013201/01/2013
302/01/20131102/01/2013
403/01/2013103/01/2013
504/01/20131304/01/2013
605/01/201306/01/2013
706/01/2013108/01/2013
807/01/201311/01/2013
908/01/2013112/01/2013
1009/01/201313/01/2013
1110/01/201315/01/2013
1211/01/20132
1312/01/20131
1413/01/201311
1514/01/2013
1615/01/20133
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! :oops:

Thanks

Ak
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I2, just enter and copy down:
Rich (BB code):
=(COUNT(B2:C2,E2:F2,H2)>0)+0
J2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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:
Rich (BB code):
=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))),"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,981
Messages
6,175,773
Members
452,668
Latest member
mrider123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top