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

conditional search [SOLVED]

Afarag

Member
I want help, I want work excel sheet that work on the search for a value according to the specific condition of the inserted values.EX: That i want insert some data like

From --------------- to ----------- Status

23801000------- 23801999------- non covered/close

22701000------- 22701999------- non covered/close

33712000------- 33712999------- non covered/close

ect...

any number start with 26,27 and 38 it's status will "non covered/close"

if any other number outside this inserted data it's status will "covered/open"

EX:if i will search for 23801770, this # that located between 23801000 23801999

the ​​required status will be non covered/close


i want make VBA for search that if my number will located between these values Show me the status,So that i can increase the inserted data those values ​​that are searched for the values ​​required

Please i appreciate your effort if you upload excel sheet that contain the required solution


ThanQ,
 
Hello,

Due to firewall restrictions on my side, I am not able to upload / download files from websites. However, if I understand your question, you want to determine the status for a value based on the first two digits of that value


If the value you are looking for is in A1, then the following formula would provide its status:

=IF(SUM(N((LEFT(A1,2)+0)={26,27,38}))>0, "Covered / Open", "Non covered / Close")


In your example, if A1 contained "23801770", it will return "Non covered / Close" since the left two digits is 23.


I did not understand the other part of your question: specifically, the use of the table in your example. Are you wanting to build a table using some rule(s)? If so, please describe the specifics.


Cheers,

Sajan.
 
Dear Sajan,

Great, i appreciate your effort; the formula go well. But i want vba code as i want make a search on a lot of value and a lot of conditional search as the above EX.


ThanQ Sajan,

Addora
 
Hello Addora,

I am sure one of the other forum regulars will respond with some VBA code. (While I dabble in Excel formulas, I try to stay away from VBA!)


Regards,

Sajan.
 
Hi, Afarag!


Your table doesn't match your explanation. You wrote that:

any number start with 26,27 and 38 it's status will "non covered/close"

but you also wrote that:

EX:if i will search for 23801770, this # that located between 23801000 23801999

the ​​required status will be non covered/close


But 23 isn't 26, 27 or 38. So could you please elaborate a bit more and clarify the whole scope of conditions? Otherwise it'd be very difficult for anyone to provide you a working code. Thank you.


Regards!
 
Back
Top