Conditional formatting of invoice lists of varying lengths for presence of a given string

[Winging It]

Hello. I should probably wait until Monday night to get a proper export of the kind I replicated here to work with if there is a possible solution, but I'm posting now in case this volunteer forum might be more active in the weekend, and there might be a greater chance of finding out if it can even be done.

I work at a store, and we'd like to compare the values of our in-person/in-store retail sales versus the online or phone order purchases.. where the latter should all have one of several Shipping Dept codes as part of their invoices (even if zeroed out for free shipping).

(And if there's a solution to that given what we have to work with, the same method could also enable us to find the average total order value of everyone who bought a given item, to help us determine whether it'd be a mistake to stop carrying it, or if a 'loss-leader' was working).

We use a POS/inventory program which *can* generate reports which can be exported to Excel to spit out the invoices as sets, starting with their number and listing either the indiv. store codes & their quantities and retail value (or the Departments those codes are subsets of) followed by the total for each invoice, to look something like this simplified version below, alright.

Problem is, they can't be filtered prior to exporting to only show those orders which include a given item (such as a shipping code) among them.

(Sadly, its filters will only tell us how many times that individual item was sold in a given interval and the value of that, not the total value of the orders they were part of. That's been confirmed by the POS support team themselves; oddly, they haven't programmed reports for orders, just for customers, sales agents, and some other things.)

Of course, the answer IS implicit in these sets of invoices it can generate. But to look at a whole year or even just a month's worth, it would take forever to manually eyeball or control-f down the first column for the instances of Shipping Department (or one of several skus for it) and highlighting / colouring the Invoice total that's part of, to isolate all the totals for all the invoices with some type of shipping attached, and do a subtotal of them.

I'm a complete novice when it comes to Conditional Formatting (and don't know how to do VBA), but from the little I've seen so far from the Q&As here and a couple of videos, it only really applies to what's on it's own row, or on its row relative to a fixed range, rather than what's in nearby rows in sets of indeterminate length.

The Q is,

IS there a way to use either it (or an 'If' function) ranging over the A column (which may be thousands of rows long, depending on the interval I'm looking at), to:

Capture / highlight only those 'begins with Total:' cells which:

have a "begins with: Shipping" (or equals "storecodeX" for the other application) in *any* row immediately above it...

but only UP TO the first instance of "begins with: Invoice) it encounters?

(Unfortunately, there are no blank lines inserted between the individual invoices in this export. If that would help, could the solution include instructions for a macro to insert a blank line after each row in the spreadsheet where Column A begins with Total: ?)

Now, the entries for individual invoices may be anywhere from 1 to about 12 rows long for the exports by Department, where Shipping will not always be the last listed one [...although I suppose I could fix that by creating a new XXShipping Parent Category/Department to reassign those codes to, though that may not go over well with the store owner], or,

....up to a couple dozen rows long for the exports done by store code, for the occasional customers who buy tons of stuff at once.

If it helps, the invoice numbers which bookend each set are presumably unique and likely of the same character length; I'll have to wait until Monday night to get back to that, if it's relevant.

	Total	# Items
Invoice 1xxxxJohnQPublic
Dept 1	75.00	3
Dept 2	8.88	2
Shipping	9.99	1
Dept 3	33.33	3
Total: Invoice 1	127.20	9
Invoice 2xxxxAnonymous
Dept 1	99.99	1
Dept 2	62.37	4
Dept 3	77.77	1
Total: Invoice 2xxxxAnonymous	240.13	6
Invoice 3xxx
Dept 1	72.13	1
Dept 2	8.88	2
Dept 5	99.99	1
Dept 6	72.13	3
Shipping	9.99	1
Dept 8	62.37	4
Total: Invoice 3xxx	325.49	12
Invoice 4xxx
Dept 1	72.13	3
Total: Invoice 3xxx	72.13	3

 

[Winging It]

Hello, again. Is it possible that your formula for ID'ing all the rows of a target invoice has a limit, and only works on dbases up to a certain number of rows?

=IF(COUNTIFS($H$2:$H$20,H2,$F$2:$F$20,"Y")>0,"Y","N")

Or could it be that somehow it works on Excel 2007 (my home version, which is the data I've been uploading here) but not on 2013?

It 'plays nice' for a month's worth of invoice data with 10,000 or so rows altogether when I make the appropriate adjustments in the differently configured column letter names and last row numbers,

but it takes a VERY long time (like a half hour) to calculate for a dbase with five year's worth of invoices with nearly half a million rows, and didn't seem to produce the right results (with N's all the way down, not the Y's where they should be)...

and then would start recalculating all over again when applying or releasing filters in the other columns.

I was going to attempt it with a year's worth, with about 120 thousand rows, but my attempt to make a file from work accessible for home failed for some unrelated reason.

 

[Alex Blakenburg]

It doesn't have limit but you right, it is not the way to go for a huge number if rows.
The volumes you are talking about should either have a database backend or use my previous data model suggestion.
You might initially be able to get away with just PQ

 

[Winging It]

You lost me: backend? and if the reference to a model is about filling a whole column with the target product description, sure I can try that, but I think the Y / N for that column did work on the whole dbase

 

[Winging It]

oh, I couldn't see your previous msg since this is now on p. 7 (sorry!), but I see now that the Data Model refers to a stage within "Power Query > Data Model > Power Pivot" .
Okay, I'll look into that sometime over the next week or so to try to suss out the learning curve required.

But I'll start by working with a year's data tomorrow to see if that works, and/or if I was simply doing something wrong today.

I don't imagine I'd be doing many of those deep dives, and could just keep sets of yearly data for that purpose and filter and reassemble the subsets if I wanted to look at multiple years' sales of a single item or brand for some reason.

 

[Winging It]

Good news: I was able to carry out the exercise I wanted to do - extracting all & only the invoices whose purchased included at least one item of our brand of water purifiers - by applying that formula to a single year's at a time.

It takes about five minutes to do the calculations for that one column [IF(COUNTIFS($H$2:$H$10000,H2,$F$2:$F$10000,"Y")>0,"Y","N") and would indeed do it again if I applied a filter after, but I could just copy and paste the values of the whole set to another sheet without formulae and filter out & delete the N-ot invoice of interest records and build up the five year subset and analyze it from there.

The object - which worked - was to get a table of customer numbers who`d bought a system or annual kit of replacement filters in some year but still hadn`t bought more filters within two years, to see if we should send a reminder notice.

With that accomplished, I can just re-use those same 1 year datasets for other individual product or brand or even just similarly named products (find:Reishi) across brands, in case we`re wondering whether it`s worth finding & bringing in another brand of something if an existing supplier dries up, so thanks again for fashioning the tools to accomplish this.

 

[Alex Blakenburg]

Thanks for the update. Glad to have been able to get you this far.