Excel help - show next non blank row but with condition
Discussion
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.
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.
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.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.
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
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
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
Awesome! Thanks for this, I will have a go tomorrow =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
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff