Help with creating a formula based on colour coding

Hi guys

I've got this big Excel sheet with loads of rules. In the spreadsheet attached, there is a Justification column that is colour coded. I have to create  another column called 'Grade'. Whatever the colour is in the Justification column, must also be added into the Grade column with the same colour. But along with that, I need the numbers put into the Grade column to. I've deliberately done an example of how it should look. So if Justification column is green coded, then the Grade column must also be filled green but with the right number, which in this case is 1. If justification column is red, then the grade column must also be red, with the associated number 4.

Can you help me with a formula that will help with filling in the correct content automatically into the grade column? I already have the justification columns completed if that makes sense.

Thanks for helping
Yashy
Firewall-Rules.xlsx
LVL 1
YashyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron MalmsteadInformation Services ManagerCommented:
YashyAuthor Commented:
Hi Ron,

Thanks for responding.

I don't believe this works by identifying the cell value with colour first and then filling in the adjacent cell with the same colour. I think there needs to be a value first, for the colours to be applied. I only have colours, and based on the colour, a value and colour will be applied to the column next to it.
Ejgil HedegaardCommented:
You can use this formula in A2, copy down.
=CHOOSE({1,2,3,4},MATCH(B2,{"Keep rule","Investigate","Change rule","Remove rule"},0))

Open in new window

And then conditional format for the value to colour.
See sheet.
Firewall-Rules.xlsx
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

YashyAuthor Commented:
Ejgil, that's amazing! It works if I have the words in your quotes in the Justification column. Is there anyway it could work with anything written in there? As the one I uploaded was an example. The justification column does have a lot of content in there with explanations etc. If there is a way that would be amazing. If however you think the only way would be to do it this way, then I'll make changes to do accommodate it.

Thank you again.
Ejgil HedegaardCommented:
The text in the quotes can be anything, but the formula requires exact match to the text in B2.

If there is not an exact match, the key words (keep, investigate, change, remove) could be searched in the text, and some if statements could define the grade number 1, 2, 3 or 4.
In this sentence "Investigate this rule before you keep, remove or change it", all 4 grade numbers could be the result, depending on the search order for the key words.
So a priority for the key words must be defined.

Another method could be to use the first key word found to define the grade number, but I think a formula for that will be very complicated.
Then I would go for a UDF function programmed in VBA (macro), which is much easier.

If you think either is the way to go, then upload a sample with some justification explanations.
And a description of key words, priority etc.

But I would suggest you separate justification and explanation, and use 3 columns, Grade, Justification and Explanation.
All columns could be coloured with conditional format using the number in the Grade column.
YashyAuthor Commented:
Ejgil, so I did try to add something like 'Keep Rule. asdfjk;asd jfk;a jkf' into the Justification column and it will give me an #N/A error. Don't worry if it's too difficult, but is there a way for it to search for a match within a set of phrases at all?

Apologies for the delay in responding.
Ejgil HedegaardCommented:
The text has to match.
If not you get the error.
See A2 where the Match function look for 'Keep Rule. asdfjk;asd jfk;a jkf' in B2.
There is exact match, so no error.

The Search function can find a text within a text.
See A7 and A8 where a series of If functions and Search functions set the grade number.
Firewall-Rules.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YashyAuthor Commented:
Worked beautifully. Thank you so much Ejgil. I really appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.