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