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

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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 175.003
Dept 28.882
Shipping9.991
Dept 333.333
Total: Invoice 1127.209
Invoice 2xxxxAnonymous
Dept 199.991
Dept 262.374
Dept 377.771
Total: Invoice 2xxxxAnonymous240.136
Invoice 3xxx
Dept 172.131
Dept 28.882
Dept 599.991
Dept 672.133
Shipping9.991
Dept 862.374
Total: Invoice 3xxx325.4912
Invoice 4xxx
Dept 172.133
Total: Invoice 3xxx72.133
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.


1633441349063.png
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
ABCDEFG
1VariableQty Sold[blank]Total RetailLine TypeSub TypeInvoice #
21-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 secInv Hdg 1-318333
3Professional Therapeutic-2-29.98DetailAllocated Cost1-318333
4Shipping-1-9.99DetailShipping1-318333
5Total: 1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec-3-39.97Total 1-318333
Data
Cell Formulas
RangeFormula
E2:E5E2=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
F2:F5F2=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")
G2:G5G2=IF(E2="Inv Hdg",LEFT(A2,8),G1)
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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.

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

20211003 Invoice no on all rows.xlsx
ABCDEFGH
1VariableQty Sold[blank]Total RetailLine TypeSub TypeInvoice #Cust No
21-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 secInv Hdg 1-31833389301
3Professional Therapeutic-2-29.98DetailAllocated Cost1-31833389301
4Shipping-1-9.99DetailShipping1-31833389301
5Total: 1-318333 Tiredof Waiting #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec-3-39.97Total 1-31833389301
Dept view 20211006
Cell Formulas
RangeFormula
E2:E5E2=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
F2:F5F2=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")
G2:G5G2=IF(E2="Inv Hdg",LEFT(A2,8),G1)
H2:H5H2=IF(E2="Inv Hdg",MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1),H1)+0
 

Forum statistics

Threads
1,147,734
Messages
5,742,863
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top