Don't know correct formula for if next row blank

[kame1986]

Please see attached spreadsheet.

Hopefully my comments are clear.

All i really want to do is have a visual aid to show me when an order has received all the goods to tell me its read for dispatch.


Ideally I would like to separate the order number by a blank row and only have one of the order numbers display some form of dispatch.

Thanks

[protonLeah]

Title updated. You would be better off putting the order number in each row and subtracting a couple of COUNTIF formulas.

[ianpage]

Solution attached, using your layout. The basic approach is to count the number of items in each order by calculating the number of rows between order numbers, then count the number of "Received"s in the relevant rows. If the number of "Received"s equals the number of items, then the Despatch column shows YES, but only on lines that also have an Order Number.

Counting the number of "Received"s is done by using a COUNTIF function, and the range is specified with an OFFSET function using each Order number as a reference position, with the size of the offset range controlled by the item count for that order. This formula is in Col A, nested in additional code that turns off the count on lines that don't have an order number.

The item count for each order is a bit trickier: it's in the newly-inserted Col B (shown with orange numerals). Before we get to that though, the single cell B1 contains a formula that counts the number of non-blank rows in the "Product Description" column: this is referenced a couple of times in the formula that's in all the other cells of Col B. Note that this cell is always specified with an absolute reference, and could actually have been anywhere on the sheet: I just chose to put it at B1 to keep things neat.

The count of items per order is started by looking to see if there's an order number in the relevant column: if there is, it then finds the next higher order number (MATCH function), and calculates the number of rows between those two order numbers. (The scope of the MATCH search for the next Order Number is restricted by the number of non-blank rows in the "Product Description" column, shown at B1.) Note that this count is done only in rows that actually contain an Order Number: rows without an Order Number just return blanks (null text is "" in code to suppress zeros that would display otherwise).

This code for counting the "items per order" is nested within a handler for the special case where the order being counted is the last one on the sheet (i.e. there is no higher order number to find): this handler again makes use of the Product rows count in B1, in conjunction with a MAX function.


Other things to note: this solution assumes the Order Numbers are in strict numerical order, and that there are no blank cells before the last specified Product, either between or within orders.

Finally, you said you would ideally like a blank row between orders. Because this solution doesn't permit one, I added some conditional formatting that draws a line right across immediately above each Order Number, so that orders are clearly separated visually, even in the absence of a blank row. Don't be tempted to add horizontal borders yourself! - that would just confuse things!

Both the essential formulas (in Cols A and B) and the conditional formatting are filled in for about the first 40 rows of the attached file: before you make entries down that far, do a Fill Down for however many rows you think you might need. And of course Col B can be hidden, to produce a layout exactly matching your original file.

Hope this helps!

[ianpage]

Sorry, duplicate posting...

[kame1986]

That is quite a complete and well thought out response and answer. I thank you for your assistance in this.

I am just trying to wrap my head around the code. This is by far the most complex code I have had to deal with so far. Truth be told, I have never needed to do much of the formula before so I am in new territory.

I am only sorry that my response is somewhat shorter given the time you must have spent responding and creating.

[ianpage]

Kame, if you've spent some time looking into the code and there are still things you don't understand, post here again and I'll lead you through it. We're all here to help each other get better at this!

Best wishes.

[ianpage]

Duplicate post, sorry.