Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with priority level
I need to take this list and pick a color by means of priority. In this example, I put a priority of 1 for the color of orange in the cell next to the color, a priority of 2 for the color gray, and a priority of 3 for the color red. I need the New List to reflect the priority that I have chosen. Sometimes I may choose only 2 levels, sometimes 4. I have been able to do this with cascaded IF statements, but managing lists of 30 or more colors can be cumbersome. In addition to this, I am managing 20 separate lists of colors. Color Priority New List red 3 orange blue gray green red black white orange 1 cyan magenta gray 2 purple Not sure if this should have been in the programming group or function group. Sorry for the crosspost. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with priority level
I often do Pareto analysis (charts ranked by frequency of occurrence) and I
use a method I think can be adapted to your need: You can use a formula like this - I have written it for cell D2 and it assumes there are column headers in row 1: =IF(ISNA(MATCH(ROW(D2)-1,B:B,0)),"",OFFSET($A$1,MATCH(ROW(D2)-1,B:B,0)-1,0)) Explanation: The If ISNA part is just to avoid the error value #N/A from showing. The OFFSET finds the color you want; working from the inside out I am finding an index number based on the current cell's row (which would be 2 for the first line in the list) and subtracting 1 from that, then finding the corresponding number in your priority list, column B. The MATCH function then gives me the row number for that priority - once I have the row number I can use OFFSET to look down in column A and find the corresponding color. The formula should be good no matter how long the list of colors is, or how many priorities are assigned. The only complicating factor is when you can have a "tie" between your priorities, which probably is not applicable for you (but is a headache for me sometimes - briefly, I need to use a COUNTIF function to find duplicates and then modify the "priority" for multiple items...) HTH! - K Dales "_SPCA" wrote: I need to take this list and pick a color by means of priority. In this example, I put a priority of 1 for the color of orange in the cell next to the color, a priority of 2 for the color gray, and a priority of 3 for the color red. I need the New List to reflect the priority that I have chosen. Sometimes I may choose only 2 levels, sometimes 4. I have been able to do this with cascaded IF statements, but managing lists of 30 or more colors can be cumbersome. In addition to this, I am managing 20 separate lists of colors. Color Priority New List red 3 orange blue gray green red black white orange 1 cyan magenta gray 2 purple Not sure if this should have been in the programming group or function group. Sorry for the crosspost. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with priority level
It seems pretty straightforward to, on the sheet,
1) sort the colors by priority 2) the New List would be =IF(B3 0, A3, "") [this copied down] Or, if you want to save the old Color list, copy it and the priorities to other columns and sort there. Are you wanting the code to do this? I have been able to do this with cascaded IF statements, but .. I'd like to see one of your IF statements. _SPCA wrote: I need to take this list and pick a color by means of priority. In this example, I put a priority of 1 for the color of orange in the cell next to the color, a priority of 2 for the color gray, and a priority of 3 for the color red. I need the New List to reflect the priority that I have chosen. Sometimes I may choose only 2 levels, sometimes 4. I have been able to do this with cascaded IF statements, but managing lists of 30 or more colors can be cumbersome. In addition to this, I am managing 20 separate lists of colors. Color Priority New List red 3 orange blue gray green red black white orange 1 cyan magenta gray 2 purple Not sure if this should have been in the programming group or function group. Sorry for the crosspost. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Priority Charge | Excel Discussion (Misc queries) | |||
Process Priority | Excel Discussion (Misc queries) | |||
priority naming | Excel Worksheet Functions | |||
Help needed with priority list | Excel Worksheet Functions | |||
Why, when I create workbook-level name does it jump it to Sheet-level ? | Excel Programming |