Avatar of Yashy
Yashy
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Yashy

8/22/2022 - Mon
Ron Malmstead

Yashy

ASKER
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 Hedegaard

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Yashy

ASKER
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 Hedegaard

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Yashy

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