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]

...and maybe a column I formula to pull out the invoice date (which is month/day/year [the above is for Sept 1), which unfortunately evidently doesn't adhere to two characters for the month/day slots

(so I can play around with seeing if I can tease out seasonal trends, or filter/concentrate certain reports for that column containing /9/ or /10/ as another means of projecting sales to le them know what needs ordering, to supplement my other methods).

 

[Winging It]

Perfect: plunked it into the Table and it filled in, all the way down.

Actually, I'll probably use it without the '0' because I noted earlier (no need to go back to that, one of my too many & too long msgs), I did an export on that & found some of the customer account #s somehow ended up being names or part names or having some stray characters like a / as prefixes.

 

[Winging It]

(even though it generates the product and customer account numbers as sequential numbers, this program somewhat annoyingly renders almost all its export values as text, except for when an item is last sold -- even barcodes, which makes it a pain when I do vlookups with suppliers' price lists to check for and implement price changes, when I sometimes have to add a column to insert a ' before the barcodes if I can't get the vendor numbers to synch up instead.)

 

[Winging It]

But if that's a bridge too far - an additional date of order column - if nothing else, I could just add a month and year column off to the right of all this and fill it in as a block when plopping additional months of sales in.

 

[Winging It]

I just adapted your formula for the cust. accnt #'s for the syntax of the "on..." intro to the datesm to get this for column I (eye):

=IF(E2="Inv Hdg",MID(A2,FIND("on ",A2)+1,FIND(" ",A2)-FIND("on ",A2)-1),I1)

Which is *almost* it, and probably good enough, since I can trim the first two characters out and then convert the text string to a date format,

but it delivers two extra characters to yields results of the form:

"n 9/x/2021"

I can either just work with that, or play around with it some more (and actually learn how to do those MID functions, on a later date when I'm not so fatigued.

Thanks again for all your help.... and patience.

 

[Alex Blakenburg]

Try this:-

=IF(E2="Inv Hdg",
            DATEVALUE(MID(A2,FIND("/",A2)-2,10)),
            I1)

 

[Winging It]

Perfect - I just had to apply the date formatting to the column after. You're a Wizard!

Now I'm eager to experiment with the different ways I can arrange the pivot tables to discover different things. And when I woke up this morning, I realized I could likely add a vlookup (or rather, the other kind that can look left) column in the Product level export database for the presence of a Shipping marker associated with an invoice number in the parallel Departmental one, in order to do comparisons on how many or the average sale value or customer totals on in-store vs online customers for a given item (to advocate for or against continuing to offer it).

Thanks ever so much for all your help. I promise to start educating myself more on all the ways to take advantage of Excel's less well known features (the ones that weren't covered in my limited training in it in the late 90s) to make things more efficient.

 

[Alex Blakenburg]

A lot has changed since the mid-90s. If can you get yourself on to Microsoft 365 there are quite a lot of new functions that save doing complex workarounds.
(eg a new one that can look left is XLookup, you will need to use Index & Match)
Also take a look at Power Query. It can often clean up data with a minimal number of steps. (although your current data is unstructured enough to require quite a bit more than the basics)
Anyway, glad I could help and thanks for the feedback.

 

[Winging It]

A lot has changed since the mid-90s. If can you get yourself on to Microsoft 365 there are quite a lot of new functions that save doing complex workarounds.
(eg a new one that can look left is XLookup, you will need to use Index & Match)
Also take a look at Power Query. It can often clean up data with a minimal number of steps. (although your current data is unstructured enough to require quite a bit more than the basics)
Anyway, glad I could help and thanks for the feedback.

Thanks, yes, I did start reading about those for this this type of this task while crossing my fingers for a savior like you for my query. Thanks for clarifying what I'd need for the earlier versions of Excel for non-left indexed lookups. I'll likely just cheat and insert a new row with an = invoice # column fxn (or cust. account #, as the case may be). I just tested that on the month long dataset to see if it messes up your formulae, and is doesn't, so I can stick with what I know there.

I've completed the Shipped vs. not analysis on three years of sales data so far, mostly on my own time before my regular shift since I'm backed up in my normal duties. (It takes extra time since this program hangs when trying to generate a departmental level report by invoice for a whole year, so I have to do 12 separate monthly ones then copy & paste the relevant parts in).

I may also need to - or ask a colleague to - fiddle with a fair number of the customer names in the invoice header rows to make the date extraction formula work, because if there's a '/' or some other anomalies in the Customers names (which tends to happen if its a joint husband / wife account), it renders an "N/A"

A disappointment about pivot tables when I briefly looked at adding the Date slot as a filter in the top left slot was that it doesn't have a text or number filter to only select a certain month like **/9/** for the results. Instead, one has to manually click multiple selections.

I may end up adding additional columns to extract just the month and year. (I've done the latter on a different database for an unrelated project), or even just filtering and copying subsets of the master invoice databases I'll build up if I want to do a quick deep dive on how many of a given item we might want to buy to sell in the next month or two if it's on sale because it will expire soon, e.g..

 

[Alex Blakenburg]

You don't want to be manually changing the source data. Try this for the invoice date.
Note: assuming the data I have didn't change the characteristics via the copy paste via the Forum, the CHAR(10) represents the line feed character before the word "on".

=IF(E2="Inv Hdg",
            DATEVALUE(MID(A2,FIND(CHAR(10) & "on ",A2)+4,10)),
            I1)

If you are combining (appending) lots of files, it may be worth using Power Query to do that step.
In PQ use the Get Data > From Folder option.