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]

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 Total	Column Labels
Row Labels	Dept 1	Dept 2	Dept 3	Dept 5	Dept 6	Dept 8	Shipping	Grand Total
1001	75	8.88	33.33				9.99	127.2
1002	99.99	62.37	77.77					240.13
1003	72.13	8.88		99.99	72.13	62.37	0	315.5
1004	72.13							72.13
1005							9.99	9.99
Grand Total	319.25	80.13	111.1	99.99	72.13	62.37	19.98	764.95

 

[Alex Blakenburg]

Can we count on the invoice number being 5 characters.
If yes try the below.

Book5
	A	B	C	D
1	Description	Total	# Items	Inv No
2	Invoice 1xxxxJohnQPublic			1xxxx
3	Dept 1	75	3	1xxxx
4	Dept 2	8.88	2	1xxxx
5	Shipping	9.99	1	1xxxx
6	Dept 3	33.33	3	1xxxx
7	Total: Invoice 1	127.2	9	1xxxx
8	Invoice 2xxxxAnonymous			2xxxx
9	Dept 1	99.99	1	2xxxx
10	Dept 2	62.37	4	2xxxx
11	Dept 3	77.77	1	2xxxx
12	Total: Invoice 2xxxxAnonymous	240.13	6	2xxxx
13	Invoice 3xxx			3xxx
14	Dept 1	72.13	1	3xxx
15	Dept 2	8.88	2	3xxx
16	Dept 5	99.99	1	3xxx
17	Dept 6	72.13	3	3xxx
18	Shipping	9.99	1	3xxx
19	Dept 8	62.37	4	3xxx
20	Total: Invoice 3xxx	325.49	12	3xxx
21	Invoice 4xxx			4xxx
22	Dept 1	72.13	3	4xxx
23	Total: Invoice 3xxx	72.13	3	4xxx
Sheet1

Cell Formulas
Range	Formula
D2:D23	D2	=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)

 

[Winging It]

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]

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]

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
	A	B	C	D	E	F	G
1	Description	Total	# Items	Inv No	Line Type	Sub type
2	Invoice 1xxxxJohnQPublic			1xxxx	Inv Hdg
3	Dept 1	75	3	1xxxx	Detail	Allocated Cost
4	Dept 2	8.88	2	1xxxx	Detail	Allocated Cost
5	Shipping	9.99	1	1xxxx	Detail	Shipping
6	Dept 3	33.33	3	1xxxx	Detail	Allocated Cost
7	Total: Invoice 1	127.2	9	1xxxx	Total
8	Invoice 2xxxxAnonymous			2xxxx	Inv Hdg
9	Dept 1	99.99	1	2xxxx	Detail	Allocated Cost
10	Dept 2	62.37	4	2xxxx	Detail	Allocated Cost
11	Dept 3	77.77	1	2xxxx	Detail	Allocated Cost
12	Total: Invoice 2xxxxAnonymous	240.13	6	2xxxx	Total
13	Invoice 3xxx			3xxx	Inv Hdg
14	Dept 1	72.13	1	3xxx	Detail	Allocated Cost
15	Dept 2	8.88	2	3xxx	Detail	Allocated Cost
16	Dept 5	99.99	1	3xxx	Detail	Allocated Cost
17	Dept 6	72.13	3	3xxx	Detail	Allocated Cost
18	Shipping	9.99	1	3xxx	Detail	Shipping
19	Dept 8	62.37	4	3xxx	Detail	Allocated Cost
20	Total: Invoice 3xxx	325.49	12	3xxx	Total
21	Invoice 4xxx			4xxx	Inv Hdg
22	Dept 1	72.13	3	4xxx	Detail	Allocated Cost
23	Total: Invoice 3xxx	72.13	3	4xxx	Total
24
Sheet1

Cell Formulas
Range	Formula
D2:D23	D2	=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)
E2:E23	E2	=IF(LEFT(A2,6)="Total:","Total",IF(LEFT(A2,7)="Invoice","Inv Hdg","Detail"))
F2:F23	F2	=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")

 

[Winging It]

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
	A	B	C	D	E	F	G
1	Description	Total	# Items	Inv No	Line Type	Sub type
2	Invoice 1xxxxJohnQPublic			1xxxx	Inv Hdg
3	Dept 1	75	3	1xxxx	Detail	Allocated Cost
4	Dept 2	8.88	2	1xxxx	Detail	Allocated Cost
5	Shipping	9.99	1	1xxxx	Detail	Shipping
6	Dept 3	33.33	3	1xxxx	Detail	Allocated Cost
7	Total: Invoice 1	127.2	9	1xxxx	Total
8	Invoice 2xxxxAnonymous			2xxxx	Inv Hdg
9	Dept 1	99.99	1	2xxxx	Detail	Allocated Cost
10	Dept 2	62.37	4	2xxxx	Detail	Allocated Cost
11	Dept 3	77.77	1	2xxxx	Detail	Allocated Cost
12	Total: Invoice 2xxxxAnonymous	240.13	6	2xxxx	Total
13	Invoice 3xxx			3xxx	Inv Hdg
14	Dept 1	72.13	1	3xxx	Detail	Allocated Cost
15	Dept 2	8.88	2	3xxx	Detail	Allocated Cost
16	Dept 5	99.99	1	3xxx	Detail	Allocated Cost
17	Dept 6	72.13	3	3xxx	Detail	Allocated Cost
18	Shipping	9.99	1	3xxx	Detail	Shipping
19	Dept 8	62.37	4	3xxx	Detail	Allocated Cost
20	Total: Invoice 3xxx	325.49	12	3xxx	Total
21	Invoice 4xxx			4xxx	Inv Hdg
22	Dept 1	72.13	3	4xxx	Detail	Allocated Cost
23	Total: Invoice 3xxx	72.13	3	4xxx	Total
24
Sheet1

Cell Formulas
Range	Formula
D2:D23	D2	=IF(LEFT(A2,7)="Invoice",MID(A2,9,5),D1)
E2:E23	E2	=IF(LEFT(A2,6)="Total:","Total",IF(LEFT(A2,7)="Invoice","Inv Hdg","Detail"))
F2:F23	F2	=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")

View attachment 48279

 

[Winging It]

(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]

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]

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!