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

Find the second value after zero

V

Villalobos

Active Member
Hello,

I am trying to find the second (based on one criteria) value which is not the zero, but I have met some difficulties. Could somebody help me where I made mistake?

This is the formulae what I would like to use (on Sheet1-B2), but doesn' t work fine:
=INDEX(INDEX(Sheet2!$D$2:$D$9;MATCH(A2;Sheet2!$A$2:$A$9;0);0);MATCH(TRUE;INDEX(INDEX(Sheet2!$A$2:$D$9;MATCH(A2;Sheet2!$A$2:$A$9;0);0)>0;0);0))

Or could somebody offer an other solution?

I have uploaded the sample .xlsx.



Thanks in advance the reply!
 

Attachments

Hi ,

Sorry if I have not understood your requirement ; can you explain in words what you wish to do ?

It seems to me that since the value in column D will be 0 when ever the corresponding value in column C is STOP , a few doubts need to be clarified :

1. Can the STOP for any SUB PN , say for instance A , be followed by another GO for the same SUB PN ?

2. Can the STOP for any SUB PN be followed by another STOP , either for the same SUB PN , or for another SUB PN ?

One example of the first question would be :

Code: 
A    A1    GO       1
A    A2    STOP    0
A    A3    GO       ?
B    B1    GO       1
B    B2    GO       2
C    C1    STOP   0
C    C2    GO      1
C    C3    GO      2
C    C4    GO      3

One example of the second question would be :

Code: 
A    A1    GO       1
A    A2    STOP    0
B    B1    GO       1
B    B2    GO       2
C    C1    STOP   0
B    B3    STOP    0
C    C2    GO       1
C    C3    GO       2
C    C4    GO       3
Narayan
 
Hello Narayan,

My only target is that to find the second value (on Sheet1 - in column B) in the list (in Sheet2 - in column D) after zero (the 0 is the first value always) to SUB PN, this could be 2 or 3 (or whatever) just not the zero.

My answers to your questions:
#1: Yes, it can follow by another GO for the same SUB PN . In your example the missing value (?) will be 2.
#2: No, it can not.
 
Hi ,

Thanks for the clarifications. However , how do we get 1 against B ? The 0 before that actually is because of a SUB PN A ; for a SUB PN of B there is no STOP which has generated a 0.

Narayan
 
There is an other method beside this columns, that is give the "STOP" and "GO" signal. This method check that are there enough components or not for the MAIN PN. If there are enough components the to MAIN PN then get "GO" signal and in the column D can see that in what rank have to process the SUB PN into MAIN PN.
 
Hi ,

Sorry , but I have not understood.

What is the function of each of the columns ? Which column comes first , and which column is derived ?

Is there any data that can be replaced by formulae ? For example , the MAIN PN data in column B can be derived from the SUB PN data in column A , though if you ask me , the data in column A should be labelled MAIN PN and the data in column B should be labelled SUB PN.

Narayan
 
Consider with your table :

Array Formula
=INDEX(Sheet2!$D$2:$D$9;SMALL(IF((A2=Sheet2!$A$2:$A$9)*(0<>Sheet2!$D$2:$D$9);ROW(Sheet2!$A$2:$A$9)-MIN(ROW(Sheet2!$A$2:$A$9))+1;"");COUNTIFS($A$2:A2;A2)))

Please just replace the semicolon ";" with commas "," coz my computer setting in Indonesia

or just see the file, is that what you looking for?

Azumi
 

Attachments

Hello Azumi,

Thank you for help, your formulae is perfect for me and solved my problem.

@Narayan
I want to thank you for your time and attention was dedicated to me.

Have a nice day!
 
You must log in or register to reply here.
Back
Top