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
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Thanks again. Didn't work at first (rendered '#value!), and after trying a few modifications, I Googled what you were up to with that "find char(10)" business and saw it was for invisible line breaks this export inserts, and luckily found the post linked later right away which suggests trying 13 if 10 doesn't work, and that did. Yay!

Turns out I'll need a similar tweak to extract the customer account #, which I should try to figure out using that same line break factor instead of continuing to lean on you. I tried messing around with inserting variations of your new fix, to no avail so far.

When working with that nearly 3 years of data today cross referenced (via a vlookup of a paste of its pivot table results) to see how many were buying a certain product and the total retail of how much other stuff they bought), I found a bunch of customers names had parentheses within them

[e.g., Johnathan (John) Doe]

which confounded the central part of your formula which capitalized on there being a ')' right after the account #'s

MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1)

I was just going to search and replace what seemed to be the ")on" after every account # in the Inv Hdg rows, but that didn't work at all - turns out there's a line break right between the ) and the on.

How to search for newline or linebreak characters in Excel?

I've just made / replaced some new names from an actual export to show the types I've things I encountered today which confound the search for ) formula (which go away when I manually delete the close bracket which occur earlier than the close invoice # string

dept sales.xlsx
ABCDEFGHI
1VariableQty Sold[blank]Total RetailLine TypeSub TypeInvoice #Customer #Date
21-318333 Johnathan (John) Doe #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 secInv Hdg 1-318333#VALUE!09/01/21
3Professional Therapeutic-2-29.98DetailAllocated Cost1-318333#VALUE!09/01/21
4Shipping-1-9.99DetailShipping1-318333#VALUE!09/01/21
5Total: 1-318333 Johnathan (John) Doe #89301) on 9/1/2021 8:42:15AM for 0.00 hrs 0.00 min 39.00 sec-3-39.97Total 1-318333#VALUE!09/01/21
61-318334 (Janie) Jane Doe #90355) on 9/1/2021 8:43:35AM for 0.00 hrs 0.00 min 28.00 secInv Hdg 1-318334#VALUE!09/01/21
7Air Quality Test120.00DetailAllocated Cost1-318334#VALUE!09/01/21
8Total: 1-318334 (Janie) Jane Doe #90355) on 9/1/2021 8:43:35AM for 0.00 hrs 0.00 min 28.00 sec120.00Total 1-318334#VALUE!09/01/21
91-318335 Purchasing Agent (ACME Inc.) #72197) on 9/1/2021 8:48:55AM for 0.00 hrs 4.00 min 29.00 secInv Hdg 1-318335#VALUE!09/01/21
10Price Match144.99DetailAllocated Cost1-318335#VALUE!09/01/21
11Professional Supplements278.25DetailAllocated Cost1-318335#VALUE!09/01/21
12Shipping10.00DetailShipping1-318335#VALUE!09/01/21
13Total: 1-318335 Purchasing Agent (ACME Inc.) #72197) on 9/1/2021 8:48:55AM for 0.00 hrs 4.00 min 29.00 sec4123.24Total 1-318335#VALUE!09/01/21
Data
Cell Formulas
RangeFormula
E2:E13E2=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
F2:F13F2=IF(E2="Detail",IF(A2<>"Shipping","Allocated Cost",A2),"")
G2:G13G2=IF(E2="Inv Hdg",LEFT(A2,8),G1)
H2:H13H2=IF(E2="Inv Hdg",MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1),H1)
I2:I13I2=IF(E2="Inv Hdg",DATEVALUE(MID(A2,FIND(CHAR(13) & "on ",A2)+4,10)),I1)
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
Like you said for customer number just make use of the Line Feed (or in your case carriage return) character.
and look for ") & CHAR(13)
Excel Formula:
=IF(E2="Inv Hdg",MID(A2,FIND("#",A2)+1,FIND(")"&CHAR(13),A2)-FIND("#",A2)-1),H1)

Note:XL2BB is changing what is a carriage return code 13 at your end to a line feed code 10 at my end.
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
ah, terrific, thanks once again.

I was wondering how to insert that compound search term:

I'd tried it with an AND and a + and with various combinations more commas and parentheses, or without the ")" at all, but not with an &. (D'oh!)

Failing that, I was also looking to using Clean &/or Substitute fxns to just remove the line breaks in the initial exports in the first place and replace them with a distinct string which would behave in the modified versions of your extraction formulae, but that wasn't going well, esp. with 13 codes, plus of course that would add a lot of extra steps and time and pilot errors...

so thanks again for coming to my rescue, yet again.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
No worries, glad to help. I didn't know XL2BB converted your Char(13) Carriage return to Char(10) Line Feed at my end so I learnt something too.
 

Winging It

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

ADVERTISEMENT

No worries, glad to help. I didn't know XL2BB converted your Char(13) Carriage return to Char(10) Line Feed at my end so I learnt something too.
yes, I noticed that too just before signing off last night when I transferred my own paste back into a blank sheet and saw the date formula no longer worked

I think our nearly 20 year old POS program (whose reports operate on a 'Crystal Reports' platform) exports in some old version of ASCII or something - it offers to save the output as pdf's or Excel and something else (but not Access, alas, although I'm useless at that anyway), but the Excel outputs are in an earlier version, just .xls not .xlsx, so unless I save it as a workbook first, whenever I add headings and apply filters to them and save with the filters on, the Excel 2013 at work notes there are compatibility issues and some formatting will be lost.
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
This is the POS program we use, whose reporting abilities and certainly its documentation on how to use the dozens of reporting capabilities it does have, are sorely lacking

 

Winging It

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

ADVERTISEMENT

Just in case Alex is still watching this thread... a follow-up (of course), on how to either conditionally filter or generate another column to isolate all (and only) the Invoice #'s on all the Detail / product item rows where someone bought at least of of a certain thing, in order to see what else was bought with it.

I`ve been playing around with trying to filter either within the enhanced data table or a pivot table configuration to isolate all the invoices containing a given sku, for those times we might want to see what else customers actually bought with that item (and not just their value), whether for loss leaders or discounted items, or to check if certain items which should not be used together were in fact bought together.

I`ve kludged / ad hoc'ed a way to try to arrive at this (manually filtering the data set for that SKU, and adding a column with a "Y" in that row for the item of interest - a shipping code, in this instance); and ,

another adapting one of Alex's formulae to put either the Invoice # or a 'Nope' into a dummy column intended to serve as a lookup index list, which I was hoping could either be used as a pivot table filter (no good: it then restricts the results to just that item) or to conditionally filter all the invoice # detail entries if it appears in that column (tried that, to no avail so far).

Jan 2017.xls
ABCDEFGHIJK
1VariableItemQty SoldRetailMonthItem of InterestLine TypeInvoice #Sub TypeCustomerInvoices of Interest
21-213846 Frere Jacque #66619) on 1/2/2017 9:44:17AM for 0.00 hrs 0.00 min 44.00 secJan.Inv Hdg1-213846 66619 
313943Marpac - Travel Alarm Clock119.99Jan.Detail1-213846Allocated Cost66619Nope
419067Living Clay - Detox Clay Powder (64 fl oz/4 lbs/half-gallon)186.99Jan.Detail1-213846Allocated Cost66619Nope
5SHCShipping & Handling - Canada10.00Jan.YDetail1-213846Allocated Cost666191-213846
6Total: 1-213846 Frere Jacque #66619) on 1/2/2017 9:44:17AM for 0.00 hrs 0.00 min 44.00 sec3106.98Jan.Total1-213846 66619 
71-213847 Dormez Vous #Ad0Adu101103) on 1/2/2017 9:46:39AM for 0.00 hrs 0.00 min 53.00 secJan.Inv Hdg1-213847 Ad0Adu101103 
820920Genestra - HMF Multi Strain (60 v-caps)151.50Jan.Detail1-213847Allocated CostAd0Adu101103Nope
921975VegiDay - Organic Virgin Coconut Oil (1.5L/50.7 fl oz)134.99Jan.Detail1-213847Allocated CostAd0Adu101103Nope
107513Pure-Le Natural - Fiberrific (500g)127.99Jan.Detail1-213847Allocated CostAd0Adu101103Nope
11Total: 1-213847 Dormez Vous #Ad0Adu101103) on 1/2/2017 9:46:39AM for 0.00 hrs 0.00 min 53.00 sec3114.48Jan.Total1-213847 Ad0Adu101103 
121-213848 Sommez Latmatina #64779) on 1/2/2017 9:52:17AM for 0.00 hrs 4.00 min 5.00 secJan.Inv Hdg1-213848 64779 
1321574Aidance - Terrasil - Multi-purpose Topical Ointment MAX Tube (50g)165.99Jan.Detail1-213848Allocated Cost64779Nope
14SHCShipping & Handling - Canada19.99Jan.YDetail1-213848Allocated Cost647791-213848
15Total: 1-213848 Sommez Latmatina #64779) on 1/2/2017 9:52:17AM for 0.00 hrs 4.00 min 5.00 sec275.98Jan.Total1-213848 64779 
161-213849 Dingdong Dong #3185) on 1/2/2017 10:31:49AM for 0.00 hrs 1.00 min 11.00 secJan.Inv Hdg1-213849 3185 
1711833Pure Anada - Sheer Radiant Tinted Moisturizer (Light) (60mL)124.99Jan.Detail1-213849Allocated Cost3185Nope
1816997Pure Anada - Pressed Mineral Blush: Tender Twig (9g)111.99Jan.Detail1-213849Allocated Cost3185Nope
198917Genestra - A-Mulsion 10,000 IU (30mL)124.15Jan.Detail1-213849Allocated Cost3185Nope
20Total: 1-213849 Dingdong Dong #3185) on 1/2/2017 10:31:49AM for 0.00 hrs 1.00 min 11.00 sec361.13Jan.Total1-213849 3185 
data
Cell Formulas
RangeFormula
G2:G20G2=IF(LEFT(A2,6)="Total:","Total",IF(AND(D2="",MID(A2,2,1)="-"),"Inv Hdg","Detail"))
H2:H20H2=IF(G2="Inv Hdg",LEFT(A2,8),H1)
I2:I20I2=IF(G2="Detail",IF(A2<>"2380","Allocated Cost",A2),"")
J2:J20J2=IF(G2="Inv Hdg",MID(A2,FIND("#",A2)+1,FIND(")",A2)-FIND("#",A2)-1),J1)
K2:K20K2=IF(G2="Detail",IF(F2<>"Y","Nope",H2),"")
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
You don't want to to do anything manually.
If you put the product you want to filter on in a Cell somewhere and perhaps even give it a range name you can use it in a formula like the below replacing my hard coded value with your cell reference (or range name).
In F2
Excel Formula:
=IF(ISERROR(FIND("Shipping & Handling",B2)),"N","Y")

To flag all the whole invoice if it includes the the product on one of the lines you can do something like this:
In K2
Excel Formula:
=IF(COUNTIFS($H$2:$H$20,H2,$F$2:$F$20,"Y")>0,"Y","N")

Now that we have a better handle on what and how to extract what you want it is probably worth looking at Power Query or VBA.
 

Winging It

New Member
Joined
Oct 2, 2021
Messages
44
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You don't want to to do anything manually.
If you put the product you want to filter on in a Cell somewhere and perhaps even give it a range name you can use it in a formula like the below replacing my hard coded value with your cell reference (or range name).
In F2
Excel Formula:
=IF(ISERROR(FIND("Shipping & Handling",B2)),"N","Y")

To flag all the whole invoice if it includes the the product on one of the lines you can do something like this:
In K2
Excel Formula:
=IF(COUNTIFS($H$2:$H$20,H2,$F$2:$F$20,"Y")>0,"Y","N")

Now that we have a better handle on what and how to extract what you want it is probably worth looking at Power Query or VBA.

Wow. Thanks ever so much. You continue to astound me with your expertise and generosity, and thanks for keeping a lookout for my string and continuing to mentor me.

It'd take me a million years to try to work that out, especially since the "ISERROR" operator was completely new to me.

I love that the Find product name doesn't have to be an exact match, because then it can capture whole brand families (we try to have all our brand lines product descriptions begin the same way with "Brand X - Item details") as well as our three types of shipping codes in one fell swoop with this method.

At first the Column K formula didn't work when I switched the item of interest to a diff. unique product on the, but I soon realized I just had to adjust the $endrow to the actual number in the database (since I'd already encountered earlier today experimenting with conditional formatting which doesn't allow a whole range as the target to look up) and of course it does work.

And you're right, of course, that I should see if the license for our Office 2013 version at work permits the Power Query add-on if I want to keep expanding what we might want to pull out the invoice data (one is how often do customers who buy an air or water filtration system from us buy the replacement filters).

I take it that'd be a lot easier for me to get semi-competent at than attempting to learn Access? (which I'm very disinclined to do, having only had a couple days training on it, some 25 years ago, and I'm getting pretty long in the tooth).
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,304
Office Version
  1. 365
Platform
  1. Windows
I do like to use databases and it is great place to store cumulative data.
Since your first step it to transform the data and that would probably involve using VBA if you wanted to use MS Access, your fast track approach might be to go Power Query > Data Model > Power Pivot.
This should be doable in 2013 but your company is falling a long way behind in the available technology.
 

Forum statistics

Threads
1,148,423
Messages
5,746,596
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