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.

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

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.