Find the nearest non blank cell in a row

B

BMPeers

New Member
Joined
Nov 5, 2004
Messages
36
I have a problem which I have been trying to solve but I need help.
Thanks in advance for any answers.

I have time series data, much of which is in complete. So for example there might be 4 years missing in the series. What I'd like is a formula which finds the nearest non-blank value both forward or backwards in time.

So for my example below, I'd like cells G2 and H2 to equal 21, and I2 and J2 to equal 51.

The formulas will be entered from row 9 downwards as a complete matrix.

2cpvkuc.jpg


Note my real dataset is many thousands of rows and more years...
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
I only know how to fill in the blank cells in each row with the last number that appeared in a non-blank cell. In your data, G2 through J2 would contain 21. I'm not sure how you'd discriminate between 'going forward' and 'going backwards.' Perhaps someone more clever can noodle it out.

Excel 2012
ABCDEFGHIJKLMNOPQ
dog
cat
sheep
chicken
fox
dog
cat
sheep
chicken
fox

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1965[/TD]
[TD="align: right"]1966[/TD]
[TD="align: right"]1967[/TD]
[TD="align: right"]1968[/TD]
[TD="align: right"]1969[/TD]
[TD="align: right"]1970[/TD]
[TD="align: right"]1971[/TD]
[TD="align: right"]1972[/TD]
[TD="align: right"]1973[/TD]
[TD="align: right"]1974[/TD]
[TD="align: right"]1975[/TD]
[TD="align: right"]1976[/TD]
[TD="align: right"]1977[/TD]
[TD="align: right"]1978[/TD]
[TD="align: right"]1979[/TD]
[TD="align: right"]1980[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]42[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]96[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]78[/TD]
[TD="bgcolor: #E2EFDA, align: right"]66[/TD]
[TD="bgcolor: #E2EFDA, align: right"]91[/TD]
[TD="bgcolor: #E2EFDA, align: right"]74[/TD]
[TD="bgcolor: #D9E1F2, align: right"]74[/TD]
[TD="bgcolor: #D9E1F2, align: right"]74[/TD]
[TD="bgcolor: #D9E1F2, align: right"]74[/TD]
[TD="bgcolor: #D9E1F2, align: right"]74[/TD]
[TD="bgcolor: #E2EFDA, align: right"]65[/TD]
[TD="bgcolor: #E2EFDA, align: right"]29[/TD]
[TD="bgcolor: #E2EFDA, align: right"]14[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]17[/TD]
[TD="bgcolor: #E2EFDA, align: right"]88[/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2EFDA, align: right"]47[/TD]
[TD="bgcolor: #E2EFDA, align: right"]51[/TD]
[TD="bgcolor: #E2EFDA, align: right"]52[/TD]
[TD="bgcolor: #E2EFDA, align: right"]96[/TD]
[TD="bgcolor: #E2EFDA, align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]30[/TD]
[TD="bgcolor: #E2EFDA, align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]84[/TD]
[TD="bgcolor: #E2EFDA, align: right"]31[/TD]
[TD="bgcolor: #E2EFDA, align: right"]40[/TD]
[TD="bgcolor: #E2EFDA, align: right"]44[/TD]
[TD="bgcolor: #E2EFDA, align: right"]83[/TD]
[TD="bgcolor: #E2EFDA, align: right"]61[/TD]
[TD="bgcolor: #E2EFDA, align: right"]57[/TD]
[TD="bgcolor: #E2EFDA, align: right"]35[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #E2EFDA, align: right"]14[/TD]
[TD="bgcolor: #E2EFDA, align: right"]82[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9[/TD]
[TD="bgcolor: #E2EFDA, align: right"]91[/TD]
[TD="bgcolor: #E2EFDA, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #D9E1F2, align: right"]87[/TD]
[TD="bgcolor: #E2EFDA, align: right"]22[/TD]
[TD="bgcolor: #E2EFDA, align: right"]98[/TD]
[TD="bgcolor: #E2EFDA, align: right"]29[/TD]
[TD="bgcolor: #E2EFDA, align: right"]74[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #E2EFDA, align: right"]59[/TD]
[TD="bgcolor: #E2EFDA, align: right"]72[/TD]
[TD="bgcolor: #E2EFDA, align: right"]98[/TD]
[TD="bgcolor: #E2EFDA, align: right"]74[/TD]
[TD="bgcolor: #E2EFDA, align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #D9E1F2, align: right"]41[/TD]
[TD="bgcolor: #E2EFDA, align: right"]31[/TD]
[TD="bgcolor: #E2EFDA, align: right"]59[/TD]
[TD="bgcolor: #E2EFDA, align: right"]26[/TD]
[TD="bgcolor: #E2EFDA, align: right"]12[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #E2EFDA, align: right"]56[/TD]
[TD="bgcolor: #E2EFDA, align: right"]84[/TD]
[TD="bgcolor: #E2EFDA, align: right"]41[/TD]
[TD="bgcolor: #E2EFDA, align: right"]70[/TD]
[TD="bgcolor: #E2EFDA, align: right"]64[/TD]
[TD="bgcolor: #E2EFDA, align: right"]14[/TD]
[TD="bgcolor: #E2EFDA, align: right"]39[/TD]
[TD="bgcolor: #E2EFDA, align: right"]56[/TD]
[TD="bgcolor: #E2EFDA, align: right"]97[/TD]
[TD="bgcolor: #E2EFDA, align: right"]39[/TD]
[TD="bgcolor: #E2EFDA, align: right"]23[/TD]
[TD="bgcolor: #E2EFDA, align: right"]54[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9[/TD]
[TD="bgcolor: #E2EFDA, align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]54[/TD]
[TD="bgcolor: #E2EFDA, align: right"]96[/TD]

</tbody>
Sheet31​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:B2))),$B2:B2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:C2))),$B2:C2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:D2))),$B2:D2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:E2))),$B2:E2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:F2))),$B2:F2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:G2))),$B2:G2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:H2))),$B2:H2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:I2))),$B2:I2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:J2))),$B2:J2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:K2))),$B2:K2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:L2))),$B2:L2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:M2))),$B2:M2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:N2))),$B2:N2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:O2))),$B2:O2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:P2))),$B2:P2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Q8[/TH]
[TD="align: left"]=LOOKUP(2,1/(NOT(ISBLANK($B2:Q2))),$B2:Q2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

wizau
  • Question Question
Shirt Size with 2x Conditions (Chest & Height)
Replies
8
Views
134
Peter_SSs
Peter_SSs
F
  • Solved
Find nearest row above with value?
Replies
4
Views
624
dreid1011
dreid1011
A
  • Question Question
FIND AND GO TO THE LAST BLANK CELL IN A CERTAIN COLUMN
2
Replies
12
Views
535
MARK858
MARK858
J
  • Question Question
Formula to find first blank cell and return another value
Replies
4
Views
679
Fluff
Fluff
N
  • Question Question
MATCH and INDEX help
Replies
3
Views
791
etaf
etaf

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

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