• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula based on Multiple conditions

Manny Singh

Manny Singh

Member
Hello Excel Masters,

Once again, I have come to seek some excel help.

Attached sheet explains the query, need to set up a formula based on multiple conditions.

Please let me know if not clear, appreciate help.

Cheers, Manny.
 
Can you explain the process in a little more detail? The relationship between Qualification and Qualified and how you arrive at the YES/NO answer is not apparent to me.

Also the significance of the List of All P-No in Col I.
 
David Evans said:
Can you explain the process in a little more detail? The relationship between Qualification and Qualified and how you arrive at the YES/NO answer is not apparent to me.

Also the significance of the List of All P-No in Col I.
Click to expand...
Hi David,

As per snapshot, there are qualification requirements on one side and other side is the qualified result so when it says one metal from P No. 5A to P No. 4 qualifies P No. 5A to P No. 4/3/1.

In my example I have shown Qualification metals as per left side of snapshot and qualified metals as per right side of snapshot, if they match the snapshot requirements, Output shows "Yes", if not it shows "No".

There is a list of all different P No. which I thought would be useful to create formula.

I don't know how to set this up and would appreciate any help.
 
Hi ,

Rules 3 , 5 , 6 , 7 , 8 and 9 are straightforward to implement , since a table listing all the possible combinations will do the job.

Rules 1 , 2 and 4 are much more general in nature , and therefore more difficult to implement , unless you use VBA , in which case it is easy.

Is VBA acceptable ?

Narayan
 
NARAYANK991 said:
Hi ,

Rules 3 , 5 , 6 , 7 , 8 and 9 are straightforward to implement , since a table listing all the possible combinations will do the job.

Rules 1 , 2 and 4 are much more general in nature , and therefore more difficult to implement , unless you use VBA , in which case it is easy.

Is VBA acceptable ?

Narayan
Click to expand...
Hi Narayan, VBA would do it, I will appreciate it.
 
Hi ,

Before we get down to coding , you need to clarify a few points.

For rule #3 , can we have the following table ?
Code: 
15E ---- 15E ---- 15E ---- 15E
15E ---- 15E ---- 15E ----  5B
15E ---- 15E ----  5B ---- 15E
15E ---- 15E ----  5B ----  5B
Narayan
 
NARAYANK991 said:
Hi ,

Before we get down to coding , you need to clarify a few points.

For rule #3 , can we have the following table ?
Code: 
15E ---- 15E ---- 15E ---- 15E
15E ---- 15E ---- 15E ----  5B
15E ---- 15E ----  5B ---- 15E
15E ---- 15E ----  5B ----  5B
Narayan
Click to expand...
Hi Narayan,

You are absolutely correct.
 
Hi ,

Can you verify that the list of combinations mentioned in the attached file is correct ?

Once you confirm , I will code the remaining 3 rules.

Narayan
 

Attachments

Hi ,

More clarifications required :

Rule 1 will be satisfied when ever all the four P Nos. are identical ; is this correct ?

Rule 2 will be satisfied if the P Nos. are in the pattern :

X ---- Y ---- X ---- Y

Rule 4 will be satisfied if the P Nos. are in the pattern :

15E ---- X ---- 15E ---- X

15E ---- X ----- 5B ---- X


Narayan
 
Hi Narayan,

You are going correct all the way mate, very keen to see what you are cooking in the background, thanks for you helping out.
 
Hi ,

See if this is OK.

The macro is a UDF , and has to be used the same way you would use a native Excel function.

Two named ranges , List_Of_All_P_Nos and Table_Of_Combinations , have been defined , and the UDF makes use of these.

Narayan
 

Attachments

Hi Narayan,

You are genius my friend, I have no idea how you have done it but it's working perfect, I would have loved to donate to you if you were staff member.

I wouldn't bother you to explain me how have you done it but how can I incorporate this to my main working sheet?

Cheers, Manny
 
Hi ,

1. Create the table of combinations , and define a named range called Table_Of_Combinations for it.

2. Define a named range called List_Of_All_P_Nos for the list of all P Nos. I assume you already have such a list in your workbook.

3. Copy the code from Module1 in the uploaded workbook to your workbook ; if you have both the workbooks open , you can just click on the module in the Project Explorer pane , and drag it to your workbook.

4. Where ever you want an output , insert a formula such as :

=UDF_Match_PNos(A3:B3,D3:E3)

where :

UDF_Match_PNos is the name of the UDF

A3:B3 is the range for the P Nos for the Qualification Metals

D3:E3 is the range for the P Nos for the Qualified Metals

Narayan
 
Hi Narayan,

I followed your instructions and it works great, you truly are a rockstar of this forum, thanks so much, if you ever released e-book on excel or VBA, I will be 1st one to buy.
 
You must log in or register to reply here.
Back
Top