Excel help - show next non blank row but with condition

Excel help - show next non blank row but with condition

Author
Discussion

anxious_ant

Original Poster:

2,626 posts

78 months

Monday 2nd August 2021
quotequote all
I'm kind of stuck here so hopefully someone more proficient in Excel could assist.
This is something I hope that I can achieve in Excel without having to import into SQL.

Below is a simplified version of the dataset, so please excuse me if it doesn't make much sense, but basically I am looking to show:

6.8 in Green cell
2 in Orange cell
5 in Blue cell

I've tried using INDEX but that returns the next non-blank. I need to somehow add a condition to match value in Column A, but can't figure out how.


MOBB

3,575 posts

126 months

Monday 2nd August 2021
quotequote all
Mrexcel forum is your friend :-)

anxious_ant

Original Poster:

2,626 posts

78 months

Monday 2nd August 2021
quotequote all
MOBB said:
Mrexcel forum is your friend :-)
Cheers smile That's where I found the INDEX formulas.
I will have a go posting there tomorrow.

The other option is to manually insert the misisng data points in Excel... wobble

hyphen

26,262 posts

89 months

Monday 2nd August 2021
quotequote all
Messing about with Index and Match etc will get you there.
VBA will get you there.

Depending on how simplified your dataset is - you could always record a macro i.e. add filters, filter to B and second column to Non Blanks, copy first entry, unfilter and paste.

NowWatchThisDrive

688 posts

103 months

Monday 2nd August 2021
quotequote all
Don't have Excel to hand so may need tweaking, but something like

=INDEX(B4:$B$13,MATCH(1,(A3=A4:$A$13)*NOT(ISBLANK(B4:$B$13)),0))

Assuming "Zone" is A1

ETA: just tried in Excel and this seems to work in B3 then autofilled

Edited by NowWatchThisDrive on Monday 2nd August 20:59

anxious_ant

Original Poster:

2,626 posts

78 months

Monday 2nd August 2021
quotequote all
hyphen said:
Messing about with Index and Match etc will get you there.
VBA will get you there.

Depending on how simplified your dataset is - you could always record a macro i.e. add filters, filter to B and second column to Non Blanks, copy first entry, unfilter and paste.
Thanks. Recording a Macro won't really help, as the gaps are not in a consistent manner.

It is a rates list with banding, however there are missing prices for some zones, and I want to just pick the next available band up, but with the same Zone. So in essense we don't need to check for banding as the data is sorted ascending by band.

One band will always have the same zones (Band 1 A,B,C, Band 2 A,B,C etc). I guess I should've shown the extra banding column in screenshot for clarity smile

It was easier if the gaps are just 1 band at a time, in consistent manner. I can use simple formula for this, but as the gaps are inconsistent it's bit of a pain.


Edited by anxious_ant on Monday 2nd August 21:27

anxious_ant

Original Poster:

2,626 posts

78 months

Monday 2nd August 2021
quotequote all
NowWatchThisDrive said:
Don't have Excel to hand so may need tweaking, but something like

=INDEX(B4:$B$13,MATCH(1,(A3=A4:$A$13)*NOT(ISBLANK(B4:$B$13)),0))

Assuming "Zone" is A1

ETA: just tried in Excel and this seems to work in B3 then autofilled

Edited by NowWatchThisDrive on Monday 2nd August 20:59
Awesome! Thanks for this, I will have a go tomorrow smile

NowWatchThisDrive

688 posts

103 months

Tuesday 3rd August 2021
quotequote all
anxious_ant said:
Awesome! Thanks for this, I will have a go tomorrow smile
No problem. Only other thing I should have mentioned is it's an array formula, so entered with CTRL+SHIFT+ENTER not just ENTER.

paulrockliffe

15,639 posts

226 months

Tuesday 3rd August 2021
quotequote all
Power Query your data then put it into Power Pivot, Anything else is an unnecessary bodge. See the light.

anxious_ant

Original Poster:

2,626 posts

78 months

Tuesday 3rd August 2021
quotequote all
NowWatchThisDrive said:
No problem. Only other thing I should have mentioned is it's an array formula, so entered with CTRL+SHIFT+ENTER not just ENTER.
Worked a treat thanks smile
Had so modify the match condition to “A4=A4:$A$13” as it was picking the next value before.