Hi all,
Move values scattered in between empty cells between two lines into the top line of those two lines:
Udklip.JPG
The goal is to have the formula count the empty cells between the two order line and offset the values into line of the order it self.
Hope it makes sense?
The attached uses an array-entered formula, so if you need to make changes to the formula you need to enter it using Ctrl-Shift-Enter.
Bernie Deitrick
Excel MVP 2000-2010
Awesome. Thank you very much.
If you have time, can you break down how it works? I'm trying improve my excel-skills. Thanks
The array-formula that I used in C2 is this:
=SUM(OFFSET(C3,0,0,MIN(IF($B3:$B$17="",ROW($B3:$B$17)))-ROW()-1,1))
When it is copied to the next set of headers (starting in C8), it becomes
=SUM(OFFSET(C9,0,0,MIN(IF($B9:$B$17="",ROW($B9:$B$17)))-ROW()-1,1))
Basically, the important part is this array MIN(IF($B3:$B$17="",ROW($B3:$B$17))) which finds the number of the first row below B3 that is empty. Then by subtracting the ROW() of the formula (2) and an additional 1, we get the size of the possible values beneath the header (but before the next), which is used with OFFSET to find the sum of those numbers.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks