Excel Formulas

B

Baffled in Excel

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.
 
J

Jim Rech

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
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.
 
J

JLatham

Could you give us some examples of how these things look both when all steps
are completed, and when all are not. Maybe something like:

Completed Example
A
1 12345
2 12345-01
3 12345-02
4 12345-03

Incomplete Example
A
5 23456
6 23456-01
7
8

That kind of thing?
 
B

Baffled in Excel

Absolutely, thanks much!

Example:
Tracking Number Description
3D4500123 Select Item
3D4500123 Box Item
3D4500123 Deliver Item

So with this I have a Description missing, which is "load item". I want
something to tell me that this step was missed...

Hope this helps
 
B

Baffled in Excel

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.
 
F

Farhad

Hi,

I think this works for you:

=IF(A2=A1,IF(OR(AND(F1+1=3,A2<>A3),AND(F1+1=2,A2<>A3)),"Wrong",F1+1),IF(AND(A2<>A1,A2<>A3),"wrong",1))

assumed your data started from A2 and the column F is empty (helper column)
so if your data starts from the first row insert a row above your data and
let it starts from the second row.

Thanks,
 
B

Baffled in Excel

Farhad,

First, you totally rock! Thanks for helping. I inserted this formulaa, but
I believe A1 is not pulling from the right cell. What is A1 looking for? I
inserted a new Row as you said and A1 is blank. I have the 'helper' cell as
column H, but it is still not working. Thanks a mil!
 
F

Farhad

Hi,

I examine this formula and it worked so now i guess it may your data is not
sorted so if my guess is correct i found another way try this:

=IF(COUNTIF($A$1:$A$100,A1)<4,"wrong","")

but at last you should sort your data.

Thanks,
 
F

Farhad

Hi again,

regarding A1:
This cell is just for comparing and just has to be defrent to A2.
the logic in the formula is that we are comparing active cell with one cell
up and one cell down and that is why i said you should start from the second
row because if your active cell would be the first row so you don't have any
cells up your active cell and then you face to an error.

Thanks,
 
J

JLatham

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.
 
J

JLatham

As promised, here's link to a workbook that has the way I described earlier
working in a worksheet. Two other worksheets in the workbook show how to use
the SUMPRODUCT() portion of it along with a list to validate the stages shown
next to order numbers, and how to use VLOOKUP() instead of the CHOOSE()
function to check on the status of any order number in a long list of order
numbers that may be all mixed up.
http://www.jlathamsite.com/uploads/ChooseItforBaffled.xls (Excel 97-2003
format 25 KB - no macros)
 
Top