Help with creating a formula based on colour coding

Yashy
Yashy used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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.
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
Ensure you’re charging the right price for your IT

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

Author

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

Author

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

Author

Commented:
Worked beautifully. Thank you so much Ejgil. I really appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial