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

Extract the list of values which are out of ranges for a given input values....

kuldeepjainesl

kuldeepjainesl

Member
Hi Experts,


Situation is : I have a long data set as given below.

[pre]
Code: 
Range_Start"A"	Range_End" B"	Value"C"

BCSA5900--------BCSA5909--------NSC27628
5232501---------5237500---------NSC27243
5237501---------5242000---------5241000
5242001---------5244500---------NDT00750
5244501---------5245500---------NSC27575
NDT00701--------NDT00950--------NSC27571
5245501---------5248500---------NSC27626
ALP99860--------ALP99868--------NSC27572
AHP87957--------AHP87966--------NSC27574
NDT00155--------NDT00314--------NSC27347
NDT00001--------NDT00054--------NSC27244
NDTM0001--------NDTM0482--------NSC27242
[/pre]
I need a list of all values in separate coloum which do not fall in the given range "A" &"B"


In case I am not clear with requirement please ask the details you will need.


Regards,

Kuldeep
 
Hi kuldeepjainesl,


Could you please explain your requirement bit more clearly so I can help you?


I would request you to further explain the following statement-


"I need a list of all values in separate coloumn which do not fall in the given range "A" &"B""


Kaushik
 
I'm guessing this is a continuation of the problem here:

http://chandoo.org/forums/topic/design-a-matrix-from-two-way-look-up-and-ranges
 
Let me explain in detail:


I have coloum A and B having a alphanumeric range.


Coloum C has values which may or may not fall under these ranges given in coloum A&B


I need the list of values which are not falling under any of the range given in coloum A &B.


If I am still fails to explain I can sand the copy of excel if you mail me at kuldeep.jain@live.com


Regards

Kuldeep
 
Yes Luke M.


You are right. This is a problem i came across when i move ahead with your formula and observed that some value may be outside of the criteria and i may be asked to get the list of those. Your kind help is required.


I have explain on that post as well.


Regards,

Kuldeep
 
Would it be acceptable to just highlight the values, or use a helper column?


Formula for conditional format or in helper column:

=SUMPRODUCT((C2>=$A$2:$A$4)*(C2<=$B$2:$B$4))=0


If you want a list somewhere else, then assuming you are using column D as helper column, array formula to generate list:

=IF(COUNTIF(D:D,TRUE)<ROWS(A$1:A1),"",INDEX(C:C,SMALL(IF($D$2:$D$1000,ROW($D$2:$D$1000)),ROW(A1))))


Remember to confirm using Ctrl+Shift+Enter. Drag down as far as would ever be needed. Un-needed formulas will simply display "".
 
Worked.......Tested for a small set of data.


I just only needed to set references as to my data


Coloum D has "=SUMPRODUCT((C1>=$A$1:$A$4)*(C1<=$B$1:$B$4))=0"


Coloum E has "=IF(COUNTIF(D:D,TRUE)<ROWS(A$1:A1),"",INDEX(C:C,SMALL(IF($D$1:$D$1000,ROW($D$1:$D$1000)),ROW(A1))))"


It gave a list of values i wanted.


Hearty Thanks

Kuldeep
 
Hi Montrey,


You first need to follow post at


http://chandoo.org/forums/topic/design-a-matrix-from-two-way-look-up-and-ranges


this is in continuation of that only. In one line these formula search a value between ranges where it fails to match, it gives a true and false and then second formula list these values based on these flag generated by first formula.


Very well suggested by Luke M.


Regards,

Kuldeep
 
You must log in or register to reply here.
Back
Top