Find a match and shade

[Frank]

Hi Sir

Thank you

I manage to understand the formula and conditional formatting

I have a further question which is a little numerology that i use with lotto

if I may

Regards

Vraj

Well I’m glad you found the formulas and worked out how I did it. Not many people can do that.
You can ask about the other problem, whether I can answer it is another matter.
Frank

 

[khanaran]

Hi Mr Frank

I neede a further help with the same sheet only this time added three tables Friends, Enemy and Neutral and the shading
Red, Green or Neutral and if no compatibility no shading

http://www.mediafire.com/file/axpcq69n8cju9if/numero_robot.xlsx/file

thank you in anticipation

Kind regards

raj

 

[Frank]

Hi Mr Frank

I neede a further help with the same sheet only this time added three tables Friends, Enemy and Neutral and the shading
Red, Green or Neutral and if no compatibility no shading

http://www.mediafire.com/file/axpcq69n8cju9if/numero_robot.xlsx/file

thank you in anticipation

Kind regards

raj

Once again you made some errors in your cell references in your spreadsheet instructions, referring to the wrong columns in the tables. Please proof read your instructions.

Your NEUTRAL table is a mess, confusing, with duplicates of the same information, with rows missing for some other numbers, and your reference numbers in the first column of that table were all jumbled up. Excel cannot work with that !

I have rationalised that table to show only relevant non duplicated information.

In order to work out whether your 2 numbers were friends, enemies or Neutral in each case, I created a truth table K3:M10 which asks the questions and if the answer is YES, has a 1, if the answer is NO has a 0 in the table.
Note that there should only ever be a single 1 on each row, since you can't be BOTH enemies AND neutral at the same time etc. If they ever are, then your tables are to blame.

The formula to ask the question "are they friends" ? uses the MATCH function to check whether the value in col H matches any value on the row (N down in the table) where N is the number in column D. If it finds a match it returns a 1 to the truth table.
Same logic for the Enemies and Neutral questions.
Conditional formatting applied to the cells in column H, asks the question " is there a 1 in the truth table friends column ? If so colour light green.
It also asks " is there a 1 in the truth table enemies column ?" If so colour red .
It also asks " is there a 1 in the truth table neutral column ?" If so colour orange .

The normal cell fill colour is clear - for not compatible as anything.

It seems to work for the number combinations you supplied, not tested for all possibilities. The danger is it deciding it is neutral AND and enemy or friend at the same time. If you spot 2 1's on the same row of the truth table, something is not right.


Check it out and see if it works OK.

http://www.mediafire.com/file/2f7gcg0c54h2y91/numero_robot.xlsx/file

 

[khanaran]

Hi Mr Frank

Thank you so much for fixing up my errors and thereafter solving my questions.

I tested it under a large number of possibilities and they all work brilliantly

I would like to know if it would be ok if I could send you a gift for all the tireless hours you placed over the years in solving my problem.

Is there anything that I can to you from here in South Africa or from India.

Thanking you

VRaj

 

[khanaran]

Sir

I have one final question to ask for a solution to finish what I have in mind.

I will plan the question carefully

Regards

Vraj

 

[Frank]

Hi Mr Frank

Thank you so much for fixing up my errors and thereafter solving my questions.

I tested it under a large number of possibilities and they all work brilliantly

I would like to know if it would be ok if I could send you a gift for all the tireless hours you placed over the years in solving my problem.

Is there anything that I can to you from here in South Africa or from India.

Thanking you

VRaj

I'm glad it all worked out Ok for you.
As for sending gifts, I have always said that I do this not for reward but to keep my brain excercised and hopefully encourage people to learn Excel for themselves, I won't be around forever. You really do not need to do this.

To answer your question though, for the benefit of everyone else- it is possible to send Amazon.co.uk egift vouchers purchased on the UK site- from anywhere in the world to UK recipents via email. Uk residents like me can redeem them on the UK Amazon site. One would only need an email address to send it to. It is important that you do not buy them in your own country's site to send abroad, you buy them in the recipients countries site, where they can then redeem them.

Sir

I have one final question to ask for a solution to finish what I have in mind.

I will plan the question carefully

Regards

Vraj

Well go ahead and ask, I'll see what I can do.