February 22, 2022

Hello great people,

My name is John Davess. I am a new member here and I am also new to Excel programming. I am in need of your help. I have uploaded a file with some information.

What I need do is this:

I have two sheets in the file SALES BOOK sheet and RELATIONSIP sheet.

In the SALES BOOK sheet, I have generated series of sales in numbers. Now, each of these numbers have its relationships with other numbers as mentioned in the RELATIONSHIP sheet.

So, if for instance, I want to check for number 73 (J8) of SALES BOOK sheet, and I want to check if the number relationships of 73 in RELATIONSHIP sheet appears in C16:G16 and I16:M16, the result should be true in cell O8. This will be true because 73,37 are relationships of 73 which I am looking for in cell J8.

PLEASE NOTE: EVEN IF THERE IS JUST ONE RELATIONSHIP, THE RESULT SHOULD BE TRUE. AS LONG AS IT DETECTS ANY RELATIONSHIP, THE RESULT SHOULD BE TRUE.

I can perform this operation with any cell. This should please be considered.

I will be glad if anyone can assist me with this.

One love.

John Davess

October 5, 2010

Hi John,

No need to post the same question in different forums, just once is enough.

The logic in your question is difficult to understand. Why for example would I check Order 13 when I select number 73 from Order 5? Where's the logic that connects these? It seems arbitrary.

In order to create any formula/code to solve your problem, we need to understand the rules/logic that drives the process.

Please explain why:

- Any particular number is chosen e.g. 73
- One a number is chosen, what determines which row(s) to check for relationships. Do you check 1 row or multiple?

Regards

Phil

February 22, 2022

Hi Phil,

I am so sorry for posting into a another group. I am new here and I didn't know. I posted in the new group because I thought I was on the wrong group before where I made the first post since I did not get any answer.

That being said, let me explain the problem to you.

NO | COUNT (A) | BON | MAL | KEY LOCK | SHADE | PART | EQUI | CODAK | LEFT TURN |

1 | 46 | 74 | 89 | 58 | 4 | 3 | 8 | 0 | 10 |

2 | 47 | 8 | 88 | 65 | 8 | 9 | 7 | 9 | 20 |

3 | 48 | 17 | 87 | 57 | 0 | 1 | 6 | 8 | 30 |

From above, I have NO. 1, 2 ,3.

1. The relationship of **NO. 1** is 46,74,89,58,4,3,8,0,10. You can find this information in the RELATIONSHIP SHEET of the attached file. So every NO. (from 1 to 90) shares a relationship with other number (Just as above).

2. Now, I have Number 1 in cell J8 of SALES BOOK sheet (Attached). I want to know if number one in cell J8 has any of its number relationships in row **C16:G16 and I16:M16 **(Yellow highlighted)

So what the formula will do is that, it will compare all the numbers in row **C16:G16 and I16:M16 **(Yellow highlighted) of the SALES BOOK sheet with column A:J of the RELATIONSHIP sheet. Since I am looking for the number 1, it will detect number 1 in column A of the RELATIONSHIP sheet and look for all the numbers in cell A3:J3 and compare those numbers with **C16:G16 and I16:M16 **(Yellow highlighted) of the SALES BOOK sheet. The result cell (O8) will be true in this case because **cell A3:J3** has 46 and cell **C16:G16 and I16:M16 **(Yellow highlighted) also has 46.

With this formula, I will be able to look at any number and check the relationship in any cell of the SALES BOOK sheet.

To answer you other question,

- One a number is chosen, what determines which row(s) to check for relationships. Do you check 1 row or multiple?

I can only check one number in one row of the SALES BOOK sheet and it will compare its number relationships in the RELATIONSHIP sheet. If any number is available, even if it is just one number detected, result should be TRUE and FALSE if no number relationship.

Please do let me know if you need more clarification

Thanks Phil.

John.

VIP

Trusted Members

December 7, 2016

Hello,

Below is an example of a formula to check if any value in a range exists in another range. This is however static and of lttle use, but as you have not described the logic of why you want to check the relationships of number 1 and not number 29 or 51 etc, and there is no obvious logic to why you want check against row 16 and not against row 15 or 20, it is impossible to write a dynamic formula.

=SUM(COUNTIF(RELATIONSHIP!B3:J3;C16:M16))>0

Br,

Anders

February 22, 2022

Hello Anders,

Thanks for taking interest in my problem. I really appreciate.

The Logic behind the whole thing has you have asked is that, if you check cell J8 of the SALES BOOK sheet, I discovered that its number relationship (46) is on row 16. Meaning that, a number at Colum J has one or two of its relationship 9 rows down and the result formula will be at cell O8. Now, since I am using a table, if the formula works, the table will automatically check for number 4 at ell J9 and check also its number relationship in row 17 (which is 9 rows down also counting from cell J9). This is the logic behind it. I may decide to check for any cell number in any row and the next automatic result will take the same pattern.

So, if I can be helped with the formula and it works, all i need do is change the row of the formula anytime I want to check other cell number in any row I so desire to check. This is the whole Idea.

Please let me know if you need more clarification.

Once again, thanks so much for the effort you are putting in for me.

Regards,

John

VIP

Trusted Members

December 7, 2016

Hello John,

Ok, if we make this parameter based then it's a lot easier. See attached file.

You just simply type the cell reference you want to check, and then the order number. The cell and row will be highlighted and the result will be displayed on the same row. Hope this works for you.

* Notice that the first total column is included in the LookupRange, if you dont want that, then the easiest solution is to move it to another location.

Br,

Anders

February 22, 2022

Hello Anders,

Thank you for your help. You got the whole concept correctly and the formula is working fine. Meanwhile, the formula should work in a way that cell O8 will be FALSE from the file you have attached instead of cell 013. This is because we are checking for 19 (K8). Meaning, all cells in column O will either have FALSE or TRUE. That is, since you have checked for cell K8 and Order No. 10 and result will be cell 08, the next automatic result since it is a table will be cell 09 which will check for 32 (cell K9 and order No. 11) and the result will also be false because number relationships of 32 are 32,77,27,58,11,8,34,67,89,23 of which none of those numbers appear in order number 11. This will continue for cell k10 against order number 12, and so on.

This is how its going to be.

Once again, thanks so so much. I know you have other engagements but you took out time to assist. May God bless you abundantly.

If you need more clarification, please let me know.

Regards,

John

VIP

Trusted Members

December 7, 2016

Hello John,

Just change the formula in cell O4 to =IF(ROW(INDIRECT($R$2))=ROW(),Result,""). Make sure it copies down to the other cells in the table column. As you probably know, the INDIRECT function is volatile, so it is better to set the indirect part of the formula as a defined name.

Br,

Anders

February 22, 2022

Hello Anders,

Thanks for the prompt response. Much appreciated.

I have edited the formula as you have instructed but still, it is not working. Kindly find attached what I am talking about.

By default, if enter for instance K8 into cell R2 and order number 10 into cell S2, if condition is true or false, the result will be displayed in cell O8 and automatically, the next cell K9 will search order number 11 and its result, either true or false will be displayed in cell O9 and so on till the last row which will terminate if the last look up row of the SALES BOOK sheet is blank. With this, I will be able to filter column O for all TRUE or FALSE.

This logic will work same way if for instance I entered G4 in cell R2 and I enter order No. 2. the result will be displayed in cell O4, and automatically, G5 will look up order No. 3, G6 will look up order no. 7 and so on.

NOTE: There is no need for there to be colors. I only used the color for presentation purpose.

Thanks once again.

Kindly let me know if you need more clarification.

Regards,

John

VIP

Trusted Members

December 7, 2016

Hello John,

So you don't want a parameter based solution then.

As far as I understand what you want to do, you are most likely better off with a VBA solution. No matter how it is done, Excel only do what you tell it to. If you take the solution I have provided, tweak it a bit (probably more) you might find a working solution to what you want. Or hopefully there is someone else out here in this forum that is able to provide a ready-made solution.

All the best and if you do get a working solution, please share. We can all learn new methods and techniques.

Br,

Anders

February 22, 2022

Hello Anders,

Thanks for your response. I am really not good in programming and even some advance formula that can solve this.

I wish someone can help me out as I am really suffering at work because of this.

Thanks anyways for your assistance so far.

God bless.

Please house, is there anybody that can help out here. I am seriously in need of your help.

Thanks

John,

July 16, 2010

I don't think you're going to get an answer for this, John. It's the switching of the values from 73 to 37 that is not something Excel is designed to do. You'd have to split all numbers into two columns, one for each digit and then somehow compare them. I don't know how you'd do that with formulas.

Trusted Members

Moderators

November 1, 2018

February 22, 2022

AND BOOOOOOOOOM!!!!! I HAVE THE ANSWER.

OH GOD **Velouria**, you are a GEM!!!

This is amazing. This is the solution!!! you got it all complete and it is working perfectly well. Oh my God! I cannot contain my Joy right now. Thanks man so so much. I am grateful to you for this. God will bless you abundantly.

WOW! and Yes, it is everything I wanted **Velouria. **Thanks.

I will go through it all over again and revert but I have checked so many cases and they are working fine.

Regards,

John

Trusted Members

Moderators

November 1, 2018

VIP

Trusted Members

December 7, 2016

February 22, 2022

Greetings Velouria,

Thanks for your assistance the other day. You have made my job a lot easier ever since. Everything is working perfectly okay.

Meanwhile, I will be needing your help once more on this project.

Before now, we have been looking for number relationships in multiple cells of a row. What I need now is to be able to check if any number relationship exists in a single cell. That is, say I want to check for relationship of No. 19 (Cell K8) in cell **C15 alone** instead of the whole row (C15:G15, I15:M15).

I have attached same file you provided.

Once this is done, then this project is over.

Thanks in anticipation of your assistance.

Warm regards,

John

Trusted Members

Moderators

November 1, 2018

February 22, 2022

Hello Velouria,

As always, thanks for your quick response and interest in my project.

I tried tweaking the formula you have provided. I couldn't get my head around it. So what I did was to go back to the original formula in the file you provided and I was able to work around it with the SUMPRODUCT function by reducing the table range of SALES BOOK. This is working now.

Thanks so so much for all the help you have provided me.

You don't know how happy I am right now.

Cheers man.

I will test some more examples to know if everything is fine

Regards,

John

1 Guest(s)