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

 

[Alex Blakenburg]

Did you mean to add a comment to that ?

 

[Winging It]

oops, another phantom repeat reply, sorry.

Well, that's certainly got the piece I was after, thank you - extracting and filling in the invoice number without having to search & replace "Total: " with "Total: Invoice" to make the original solution work.

And as I said above (sorry to have been burying you in msgs), I can adapt your previous "Shipping" Sub Type differentiator with the individual "product number" of whatever we might be interested in seeing how many orders it figures in and their net value, to use this for our second type of query.

I don't really understand what the final columns are for, though; don't they just repeat what's in the columns D & E of the original export? (Perhaps I mislabeled Retail; it is indeed the total dollar amount for the total number of each type that was purchased.

At rate, I think you've done it, once again, thank you. I'll experiment with a larger dataset tomorrow.

 

[Winging It]

no, sorry, that repeat bedtime post was another errant mouseclick. I'm quite tired, now, though, and truly am off to bed. Thanks again for all your help.

 

[Alex Blakenburg]

I don't really understand what the final columns are for, though; don't they just repeat what's in the columns D & E of the original export? (Perhaps I mislabeled Retail; it is indeed the total dollar amount for the total number of each type that was purchased.

This is to get the Qty Sold and Retail Amount to be consistently in the same column for both the Detail Lines and the Total Lines, so that you can report on either or both using the same pivot table or filter.

 

[Winging It]

oh, okay, thanks, I'll try that out tomorrow. Good night (or G'day?).

 

[Winging It]

back at work, about 6 hrs sleep & a little over 8 hrs later, about to try things on a month's worth.

This is being greedy for your time & expertise, now, but it occurred to me when I awoke that what we'd also like would be a similar formula to extract the customer account # instead of the invoice # from that invoice header line to fill in to all the Departments or Product # rows.

That's for the real-world issue of the owners deciding on, or doing a post-mortem on the consequences of, dropping a relatively popular product due to supplier or cost or some other issue. Some of us raise the hypothetical that we might lose the customer for good and all their other purchases when that happens, but that's just hunch rather than data driven as to how consequential that would be. The main goal would be to find their total amount of purchases over the year (or perhaps ever, if I build a master database going back ten years or so) if that item is among their purchases.

The good news there as far as a MID or some other extraction function is that even though the names between the fixed length invoice string and the customer account # string can be of indeterminate length, the reports DO code those actual customer account #s with consistent 'fences': a # and ), like this: (pasting it in here shows the hidden line breaks which don't show up when pasted into notepad but do in Word, as line rather than paragraph breaks.

1-320151
Doe John #91583)
on 9/30/2021 8:58:19AM for 0.00 hrs 3.00 min 21.00 sec

It would be the "91583" part we would be trying to isolate / fill in with.

I just did a 2 year customer export to see if those customer account #'s are all of a fixed five-character length.

Unfortunately, no. Some, presumably for the earliest / more long-standing customers, are 4. Some have stray (one with a leading /; several like this: AD1SEN101987) characters in them, which are indeed part of their customer-ID no. when looking them up in the customers section of the program. Some are names, or parts of names, with no number. And one's even blank in the customer account # (but not name column), and yet has sales showing for it. Not surprisingly, looking that one up (even copying and pasting it in case there was a hidden character) in the customer database turns up naught.

Plus of course, all the No Account / Cash customer ones get lumped together into one dummy account, but that would just be manually filtered out.

But regardless of their length or syntax, presumably they'd all appear between those #fences) in those invoice header and total rows:

1-123456
Name Name-Name #xxxxx)
on precise time the invoice was generated

 

[Winging It]

Oh, wait, it will be easier than that for tracking customer purchases:

this type of report (partial screen shot for how it's selecteed) can be configured to still organize the results by invoices but leave out the invoice no's altogether and just make the customer name & account # the bracketing entries,

Doe John #78255
....
Total: Doe John #78255

with the same columns and configuration as the Post 18 tables (where their actual headers, which don't export, are QTY OH (which isn't relevant for this exercise; I think I can leave it out of the export, it's just that I use it for keeping tabs on when we need to order stuff or limit their availability or take them off the website)), QTY SOLD; Total Price; & Vendor PLU (again, not relevant for this; PLU is what this program calls SKUs). And mysteriously the exports but not the print outs have a blank column somewhere in there.

But again, the issue is, can the MID function just pull out and fill in those codes to the right of the '#' in the export even if the strings both to the left of it are of indeterminate length.

 

[Winging It]

Success!

Had to wait until returning home again, because I tried to generate a whole year's worth of invoices by department, and the program still hadn't completed the results by the time I left about 8 hours later. I'll see if it can do it overnight; otherwise I'll have to knit together a series of monthly exports into a master database.

But I just imported a month's sales by Dept and tweaked your two original formulae and the sequel on just the 1-123456 syntax to refer to the right columns, and it works brilliantly.

Below is an excerpt for just the month's initial invoice (which was for someone getting a refund, presumably for something ordered online that we couldn't get ahold of soon enough and she she tired of waiting for) showing the amended formulae which performed as intended.

I checked the total number of invoices showing up in the pivot table of results against the original dataset, and filtered the results for possibly exceedingly high total value, and both checked out, for there being no anomaly of misclassifying a great many of the last invoices as being the same one as happened yesterday, so it looks like that was indeed due to some type of pilot error on my part when trying to insert the Invoice phrase in (I'd started with a formula for that instead of just a search & replace, which I then mucked up too), since there were nearly 2,000 invoices in this dataset. (The same set I'd messed up with, I think.)

The best news, although this is very preliminary since it's only a month's worth of data, is that this most likely isn't going to backfire on me and cost me my job (which was originally solely for tending to our website content), since the data actually shows that:

last month, even though only one-third of our orders were shipped, they accounted for two-thirds of the net retail proceeds (excluding the actual shipping fees, even when those were levied).

Mind you, for a whole year's worth, I suspect it's more like 25% of the net retail, especially pre-Covid, but we'll see.

At any rate, it's nice to be able to coax that information out of this kludgy inventory and POS program at last (which has that information in it, alright - it's what spawns these orders in the first place, even though it's developers haven't seen fit to make it easy to spit out with filters on objects of interest).

So thanks again for showing me how to do that, and for putting me wise to using defined tables to make the subsequent exports or a master database go easily.

Time for my late supper (and then feeling drowsy after), but it'd still greatly appreciated if you could devise a column H in the below to pull out and fill in the customer's account number (89301, in this case) so we could analyses by customer over a longer time frame (whether for how many times they kept buying a certain departmental type, which I'd switch out for the word "Shipping", or a given product, which I'd do on the other template you did yesterday, where I'd just tweak the column references again.)

dept sales.xlsx
	A	B	C	D	E	F	G
1	Variable	Qty Sold	[blank]	Total Retail	Line Type	Sub Type	Invoice #
2	1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec				Inv Hdg		1-318333
3	Professional Therapeutic	-2		-29.98	Detail	Allocated Cost	1-318333
4	Shipping	-1		-9.99	Detail	Shipping	1-318333
5	Total: 1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec	-3		-39.97	Total		1-318333
Data

Cell Formulas
Range	Formula
E2:E5	E2	=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
F2:F5	F2	=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")
G2:G5	G2	=IF(E2="Inv Hdg",LEFT(A2,8),G1)

 

[Alex Blakenburg]

pull out and fill in the customer's account number (89301, in this case) so we could analyses by customer over a longer time frame (whether for how many times they kept buying a certain departmental type, which I'd switch out for the word "Shipping", or a given product, which I'd do on the other template you did yesterday, where I'd just tweak the column references again.)

I will take a look.

 

[Alex Blakenburg]

Try this:
If you want the customer number to be text drop the "+0" at the end. It just converts the text to a number.

=IF(E2="Inv Hdg",
            MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1),
            H1)+0

20211003 Invoice no on all rows.xlsx
	A	B	C	D	E	F	G	H
1	Variable	Qty Sold	[blank]	Total Retail	Line Type	Sub Type	Invoice #	Cust No
2	1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec				Inv Hdg		1-318333	89301
3	Professional Therapeutic	-2		-29.98	Detail	Allocated Cost	1-318333	89301
4	Shipping	-1		-9.99	Detail	Shipping	1-318333	89301
5	Total: 1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec	-3		-39.97	Total		1-318333	89301
Dept view 20211006

Cell Formulas
Range	Formula
E2:E5	E2	=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
F2:F5	F2	=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")
G2:G5	G2	=IF(E2="Inv Hdg",LEFT(A2,8),G1)
H2:H5	H2	=IF(E2="Inv Hdg",MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1),H1)+0