This is going to get a little, well, baffling ... so I'll take it one step at
a time to arrive at a very long formula.
You've got 4 steps. Assign a binary value to each step:
Select = 1
Box = 2
Load = 4
Deliver = 8
Why powers of 2? Because added together in any combination they come up
with unique values for that combination and you can figure out what is
included or excluded from the group.
In my setup sheet, I placed the words "Select Item"..."Deliver Item" in
cells D1, D2, D3, D4 for quick referencing in the following formulas rather
than having to spell them out in the formulas.
Now, using SUMPRODUCT, you can determine if a pair in the list (A2 to B4) in
my setup which looks like your list with the missing "Load Item" entry.
=SUMPRODUCT((A2:A4=A2)*(B2:B4=D1)*1)
is a test for a match with both the order number and "Select Item" in a row,
and if it exists in the list, returns 1, otherwise returns 0.
Similar SUMPRODUCT() formulas for the range of entries will return results
based on phrase in column B matching one of the D1:F1 entries. But in each
formula, we change that *1) above to *2), *4) and *8) depending on the match
we're testing for.
When we add the results of those up, we come up with values from 0 to 15!
and the beginning of a really ugly formula looks like this (note I expanded
area from rows 2:4 to 2:5 to allow for 4 possible entries)
=SUMPRODUCT((A2:A5=A2)*(B2:B5=D1)*1) + SUMPRODUCT((A2:A5=A2)*(B2:B5=E1)*2) +
SUMPRODUCT((A2:A5=A2)*(B2:B5=F1)*4) + SUMPRODUCT((A2:A5=A2)*(B2:B5=G1)*8)
As I said, that formula will give us a value from 0 through 15. This would
be great to use with the CHOOSE() function which takes form of
=CHOOSE(#,"first choice", "2nd choice",...)
Where # is a numeric value and the choices have a 1 to 1 correspondence with
the possible values of #. Unfortunately it doesn't handle zero very well.
But we can overcome that limit by adding 1 to the result of our long formula
above.
By analysis, I know what processes are missing based on the returned value
of the long formula (+1) and we can set up a formula like this:
="Steps Missed: " & CHOOSE(SUMPRODUCT((A2:A5=A2)*(B2:B5=D1)*1) +
SUMPRODUCT((A2:A5=A2)*(B2:B5=E1)*2) + SUMPRODUCT((A2:A5=A2)*(B2:B5=F1)*4) +
SUMPRODUCT((A2:A5=A2)*(B2:B5=G1)*8)+1,"All !","Box, Load, Deliver", "Select,
Load, Deliver","Load, Deliver", "Select, Box, Deliver", "Box, Deliver",
"Select, Deliver","Deliver", "Select, Box, Load", "Box, Load", "Select,
Load", "Load", "Select, Box", "Box", "Select","All Completed")
I'll upload a workbook with this solution laid out in it, and a couple of
optional ways to deal with it so that you can choose how (and even if) this
solution or a modification of it will do what you need.
"Baffled in Excel" wrote:
Thanks much Jim, that is a start. I'm looking something to recognize the
four required entries and tell me when one is missing. That formula is a
start though.
"Jim Rech" wrote:
If I had an entry in A1 and I wanted a formula to tell me if A2 through A5
did not have the same entry, I'd put this formula in a nearby cell like A2:
=IF(SUMPRODUCT((A1=A2:A5)*1)=4,"","Incomplete")
I doubt this is what you want but maybe it's a start.
--
Jim
"Baffled in Excel" <Baffled in wrote in
message ...
|I have tracking numbers that have four similiar beginnings. For each
| tracking number there "should" be four steps attached to them. I want to
| create a formula or macro that tells me which tracking number does not
have
| all four steps on it. Each row in Excel is the tracking number, then
there
| are subsequent rows for each step for that tracking number, with that
| tracking number listed on all four rows. Maybe the formula says True or
| False or a macro that could cull them out onto a separate spreadsheet??
Tks
| for helping.