Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting or Searching?
I am still designing a purchase order form for automotive sales and now have
a new question. For example, if I have a ZPP Premium Package that includes a list of options that has its own codes, i.e. ZPP = 319, 4NA, 430, 431, 403..... how can I get Excel to automatically pick up ZPP if I receive a full list that have other options? For example, I have an automobile that has the following list of options: 205, 465, 319, 203, 4NA, 4AB, 430, 431, 639 How can I get Excel to recognize that ZPP is part of this list? Your assistance (again) is greatly appreciated. -- Erwin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting or Searching?
Here's one way. It assumes the option codes are entered in the range
B1:B9, which you'll need to change to match your spreadsheet. This formula assumes that numbers such as 319 and 403 are entered as numbers, not text. If they are entered as text, use the second version of the formula. The formula results in TRUE or FALSE: if all 5 constituent codes for ZPP are present, the formula results in TRUE. You can use that in an IF statement to handle the ZPP situation as appropriate. =AND(SUMPRODUCT(--(B1:B9=319))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9=430))=1,SUMPRODUCT(--(B1:B9=431))=1,SUMPRODUCT(--(B1:B9=403))=1) If option code numbers are entered as text: =AND(SUMPRODUCT(--(B1:B9="319"))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9="430"))=1,SUMPRODUCT(--(B1:B9="431"))=1,SUMPRODUCT(--(B1:B9="403"))=1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting | New Users to Excel | |||
Sorting Data in ascending order | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |