Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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? "Baffled in Excel" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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 "JLatham" wrote: 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? "Baffled in Excel" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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, -- Farhad Hodjat "Baffled in Excel" wrote: 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 "JLatham" wrote: 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? "Baffled in Excel" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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! "Farhad" wrote: 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, -- Farhad Hodjat "Baffled in Excel" wrote: 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 "JLatham" wrote: 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? "Baffled in Excel" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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, -- Farhad Hodjat "Baffled in Excel" wrote: 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! "Farhad" wrote: 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, -- Farhad Hodjat "Baffled in Excel" wrote: 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 "JLatham" wrote: 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? "Baffled in Excel" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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, -- Farhad Hodjat "Baffled in Excel" wrote: 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! "Farhad" wrote: 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, -- Farhad Hodjat "Baffled in Excel" wrote: 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 "JLatham" wrote: 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? "Baffled in Excel" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas
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/C...forBaffled.xls (Excel 97-2003 format 25 KB - no macros) "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
Excel formulas | Excel Discussion (Misc queries) | |||
formulas in excel | Excel Discussion (Misc queries) | |||
excel formulas | Excel Discussion (Misc queries) |