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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Update: so, if conditional formatting (or index and match, or some other techniques) are unlikely to accomplish this,

I did work out a way to get the results I was after if dealing with a Departmental level report which only has about 12 variables per order - with a pivot table, whose results can be filtered to exclude the invoices without Shipping Dept values, to get the subtotals for just those which do have them.

But to make that work, I'd need to insert the invoice numbers (or a proxy for them) in column D of each row:

so I would be seeking one or more formulae to extract the invoice number from the ungodly string the report generates at the start of each set, and put it in each row associated with that order until reaching a blank cell in column B or C, and then start over.

(possibly by doing the initial extraction with one formula for that in the rows beginning with "Total: " for each order - I'll probably be able to work that one out with Mr. Google's help -- and using some other formula dragged up from the bottom of the database (with those Total rows filtered out) to repeat that value until reaching a blank, at which point it would fetch the next one? That second feat's likely a lot tougher to pull off.

Sum of TotalColumn Labels
Row LabelsDept 1Dept 2Dept 3Dept 5Dept 6Dept 8ShippingGrand Total
1001758.8833.339.99127.2
100299.9962.3777.77240.13
100372.138.8899.9972.1362.370315.5
100472.1372.13
10059.999.99
Grand Total319.2580.13111.199.9972.1362.3719.98764.95
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
Can we count on the invoice number being 5 characters.
If yes try the below.

Book5
ABCD
1DescriptionTotal# ItemsInv No
2Invoice 1xxxxJohnQPublic1xxxx
3Dept 17531xxxx
4Dept 28.8821xxxx
5Shipping9.9911xxxx
6Dept 333.3331xxxx
7Total: Invoice 1127.291xxxx
8Invoice 2xxxxAnonymous2xxxx
9Dept 199.9912xxxx
10Dept 262.3742xxxx
11Dept 377.7712xxxx
12Total: Invoice 2xxxxAnonymous240.1362xxxx
13Invoice 3xxx3xxx
14Dept 172.1313xxx
15Dept 28.8823xxx
16Dept 599.9913xxx
17Dept 672.1333xxx
18Shipping9.9913xxx
19Dept 862.3743xxx
20Total: Invoice 3xxx325.49123xxx
21Invoice 4xxx4xxx
22Dept 172.1334xxx
23Total: Invoice 3xxx72.1334xxx
Sheet1
Cell Formulas
RangeFormula
D2:D23D2=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Wow, that's great that a single formula can accomplish that in one fell swoop....

(I just verified it on my little model, as I guess you did, fully expecting it to just produce the first invoice no. all the way down)

....thank you very much, Mr. (or possibly Ms.) Blakenburg!

I think they may actually be about 15 digits long in these reports, oddly enough; I'll have to wait until returning to work on Monday to confirm

(odd because, looking at a couple of receipts I have here for my own purchases there, the actual invoice #'s are only 6 digits long preceded by a 1- (for the store #: we used to have 2 brick&mortar stores) when first generated by the POS program, when/as the sale is actually processed.

But presumably I'll only need to change the '5' in your formula accordingly (and possibly tack on whatever common elements the beginning of those overlong invoice numbers might have to the part enclosed by quotes before the MID, to reduce the clutter)

So this workaround is 99% likely to solve my first "What portion of our gross retail sales are shipped and what's their total and average worth" query.

But I still wonder if anyone else can help with the second query, if only with a fairly definitive "no" -

Is there some type of conditional formatting (or If formula) or index/match or lookup formula solution - to isolate only those rows beginning with Total: in this type of ungainly report (which could now be augmented by putting the actual unique invoice # beside each line, thanks to Alex) - which have a given store code / SKU as a component / member of that invoice?

Because even though we're a fairly small family- (not mine) operated store, we carry thousands of individual products, so a pivot table like I did at the departmental level would be thousands of columns wide for a full year's sales of individual SKUs, and I wonder if it would crash or take an exceedingly long time to process.

I suspect it could just then be imported to MS Access to easily generate a proper answer (Query: show Totals of Invoices containing SKU xx), but I'm useless with Access. Maybe I should look into that now...
 

Winging It

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

ADVERTISEMENT

Yucch (sp?). I just poked around with having to figure out how design databases and run queries with MS Access for this purpose (which I've only ever had a couple days of instruction on, about, gulp, 25 years ago), and really don't want to have to deal with learning that program.

So now I'm thinking for my second query, if nothing else, I can simply keep using the few 'hammer, screwdriver, pliers and duct tape' type tools I do know how to use:

1) Start with the ungainly "invoices by individual products" export, and add the extra column to fill in the associated invoice # in each row, with Alex's formula; then:

2) Filter that for all the instances of an invoice including a purchase of that SKU for whatever interval that report covers;

3) alt-; copy that subset and paste and save it in a separate tab as "SKUxx purchases"; and,

4) use that for a vlookup (or maybe that other kind of lookup that can look left rather than right, if I don't want to create a new column of the invoice numbers in Column A) of all the Invoice Totals, to flag the ones whose invoice number also appears in that subset with the item of interest.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
Solution wise it is about what you are comfortable with. Other options include
• VBA
• Power Query
I do like MS Access if you are building a cumulative database.

You can do quite a lot in Excel if you are happy to use the layout I have below.
The theory is that you keep the data area in the same block as it comes so that each time you get new data you just have to delete the data on the left side and on the right side just make sure the formulas fo down to cover all the rows.
If you use Excel Tables then the Table will functionality will ensure the Formulas always cover the rows and by using the Table Name as the Pivot data source it will also pick up all the rows when you refresh it.

20211003 Invoice no on all rows.xlsx
ABCDEFG
1DescriptionTotal# ItemsInv NoLine TypeSub type
2Invoice 1xxxxJohnQPublic1xxxxInv Hdg 
3Dept 17531xxxxDetailAllocated Cost
4Dept 28.8821xxxxDetailAllocated Cost
5Shipping9.9911xxxxDetailShipping
6Dept 333.3331xxxxDetailAllocated Cost
7Total: Invoice 1127.291xxxxTotal 
8Invoice 2xxxxAnonymous2xxxxInv Hdg 
9Dept 199.9912xxxxDetailAllocated Cost
10Dept 262.3742xxxxDetailAllocated Cost
11Dept 377.7712xxxxDetailAllocated Cost
12Total: Invoice 2xxxxAnonymous240.1362xxxxTotal 
13Invoice 3xxx3xxxInv Hdg 
14Dept 172.1313xxxDetailAllocated Cost
15Dept 28.8823xxxDetailAllocated Cost
16Dept 599.9913xxxDetailAllocated Cost
17Dept 672.1333xxxDetailAllocated Cost
18Shipping9.9913xxxDetailShipping
19Dept 862.3743xxxDetailAllocated Cost
20Total: Invoice 3xxx325.49123xxxTotal 
21Invoice 4xxx4xxxInv Hdg 
22Dept 172.1334xxxDetailAllocated Cost
23Total: Invoice 3xxx72.1334xxxTotal 
24
Sheet1
Cell Formulas
RangeFormula
D2:D23D2=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)
E2:E23E2=IF(LEFT(A2,6)="Total:","Total",IF(LEFT(A2,7)="Invoice","Inv Hdg","Detail"))
F2:F23F2=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")


1633310462859.png
 
Solution

Winging It

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

ADVERTISEMENT

Solution wise it is about what you are comfortable with. Other options include
• VBA
• Power Query
I do like MS Access if you are building a cumulative database.

You can do quite a lot in Excel if you are happy to use the layout I have below.
The theory is that you keep the data area in the same block as it comes so that each time you get new data you just have to delete the data on the left side and on the right side just make sure the formulas fo down to cover all the rows.
If you use Excel Tables then the Table will functionality will ensure the Formulas always cover the rows and by using the Table Name as the Pivot data source it will also pick up all the rows when you refresh it.

20211003 Invoice no on all rows.xlsx
ABCDEFG
1DescriptionTotal# ItemsInv NoLine TypeSub type
2Invoice 1xxxxJohnQPublic1xxxxInv Hdg 
3Dept 17531xxxxDetailAllocated Cost
4Dept 28.8821xxxxDetailAllocated Cost
5Shipping9.9911xxxxDetailShipping
6Dept 333.3331xxxxDetailAllocated Cost
7Total: Invoice 1127.291xxxxTotal 
8Invoice 2xxxxAnonymous2xxxxInv Hdg 
9Dept 199.9912xxxxDetailAllocated Cost
10Dept 262.3742xxxxDetailAllocated Cost
11Dept 377.7712xxxxDetailAllocated Cost
12Total: Invoice 2xxxxAnonymous240.1362xxxxTotal 
13Invoice 3xxx3xxxInv Hdg 
14Dept 172.1313xxxDetailAllocated Cost
15Dept 28.8823xxxDetailAllocated Cost
16Dept 599.9913xxxDetailAllocated Cost
17Dept 672.1333xxxDetailAllocated Cost
18Shipping9.9913xxxDetailShipping
19Dept 862.3743xxxDetailAllocated Cost
20Total: Invoice 3xxx325.49123xxxTotal 
21Invoice 4xxx4xxxInv Hdg 
22Dept 172.1334xxxDetailAllocated Cost
23Total: Invoice 3xxx72.1334xxxTotal 
24
Sheet1
Cell Formulas
RangeFormula
D2:D23D2=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)
E2:E23E2=IF(LEFT(A2,6)="Total:","Total",IF(LEFT(A2,7)="Invoice","Inv Hdg","Detail"))
F2:F23F2=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")


View attachment 48279
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
(sorry about the preceding message; this darn Logitech trackball mouse acts up with unwanted double clicks and sent a reply instead of just opening one up; I can't seem to delete that msg)

Brilliant. Thanks for looking in again and adding a value-added piece, to collapse the names of the Departments for a more simplified presentation of that one issue (presence of shipping in order).

I just copied it into a spreadsheet to replicate how you set up the pivot table: very elegant. (My way was to put the first variables column in the filters column with 'Does not contain:Invoice")

Your set up will also do nicely for my second query, then, to collapse all the other items into just one lump 'Allocated Costs' when I export by individual product SKUs rather than Departments: then I can just swap out the word Shipping for the SKU # if we want to see how many invoices it appears in, in a given interval, and the value of those orders.

I don't really understand what you're referring to about the formulae in your columns being able to auto fill themselves as far down as needed if I keep a Named table as a template or what a named table is or how that works, though...

I'll look into that tomorrow night, but presumably I can just do what I usually do with such things: copy it from an existing headings-plus-formulas rows as the template, ctrl-end to the bottom of the adjacent columns of a fresh export and paste it and fill it in from the bottom up.

Thanks again: I'll report back tomorrow night after trying your formulae & template out on a week's worth of actual data, to start with.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
Here is a youtube video by Wyn Hopkins (Access Analytics) that might wet your appetite, for Excel Table
(13 mins)
In the meantime, thanks for your feedback. Glad to help and yes, let me know how it works out for you.

.
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
ah, thanks again - that is a real eye-opener and will bear watching multiple times to use for the reports I regularly generate on inventory levels and what needs ordering.

I thought that Table section was just for formatting, to make it look prettier, so always cancel out of it when Excel starts to apply it (even without 'Clippy' it still pushes some things), since that would just waste printer ink with the background shading.

...and didn't realize it can autofill formulae, like you were telling me; and keep showing header row as one scrolls down (without having to split screen & freeze top rows); and redefine the range of pivot tables when you add rows and refresh, and the other things he runs through in that video.

I see now I've been doing too many things in Excel the hard way, as I've been taking on more admin support roles in this small biz of about 10 employees. (I was hired as a writer for their website content; but have been doing more & more of this sort of thing, since I'm better at Excel than the rest, despite only having an intermediate level of competence with it.) It's about to get much better with your help, thanks!
 

Forum statistics

Threads
1,148,417
Messages
5,746,553
Members
424,032
Latest member
pochie2741

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