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

V

vaibhav tandon

Member
Hi All,


I am new to this board and this is my first post :)


The problem goes like this:

In my first sheet I have data in which Component Name(which is actually a project phase) has multiple defects and it has status which is either closed or open.


Component Name Defect Description Status

LNZAR98D "STEP020 - the COND=(4,LT) should be removed Closed

"STEP020 - The new file CURRSUSP" Closed

Remove BLKSIZE Closed


LNZKF30D STEP01, STEP02 etc to be renamed to STEP010 Open


In my second sheet I wsnt to have


Component Name Status

LNZAR98D Closed

LNZKF30D Open


So my problem/issue is that in the second sheet I want "Status" column to be populated automatically based on the values in First sheet.Which means if the last defect of a component name has status "closed" in first sheet then in second sheet the status column should have status as "closed" and if the last defect of a component name has status "open" or "its a blank" in first sheet then in second sheet the status column should have status as "open".


So is there any in excel formula that can be used to get this data in second sheet?If not is this possible through Macro?


I hope that I have explained the problem and would be easy to comprehend.Thanks in advance.


Regards

Vaibhav
 
Hi vaibhav tandon,


Welcome to the Chandoo's Forums. Please read the Three Green Sticky posts on the forums home page that will make your working with this forum easier :)


Assuming following data to be present in Sheet1 between A1:D11...

[pre]
Code: 
Component	Name~Defect	Description	Status
LNZAR98D	STEP001		                Open
LNZAR98D	STEP002		                Open
LNZAR98D	STEP003		                Open
LNZAR98D	STEP004		                Open
LNZAR98D	STEP005		                Closed
LNZKF30D	STEP001		                Closed
LNZKF30D	STEP002		                Closed
LNZKF30D	STEP003		                Closed
LNZKF30D	STEP004		                Closed
LNZKF30D	STEP005		                Open
...sheet2 having this layout between A1:B3

[code]Component	Status
LNZAR98D
LNZKF30D
[/pre]
...enter following formula in B2 & B3 with Ctrl+Shift+Enter:


=INDEX(Sheet1!$D$2:$D$11,LARGE((Sheet1!$A$2:$A$11=A2)*ROW(Sheet1!$A$2:$A$11)-1,1))[/code]


Hope that works!


Regards,

Faseeh
 
Thanks Faseeh for the quick help an dreply.this works like charm.Out of curiosuty my next question comes,can this be implemented through VBA?would it be difficult or simpler to implement through that?


Regards

vaibhav
 
Faseeh,


1 more thing that I noticed,if I add a row in between or in end then the values are not reflecting.In 1 sheet if I add values(ones not having Name) then the status doesnt change in 2 sheet.


Component Name Description Status

LNZAR98D Step001 Open

LNZAR98D Step002 Open

LNZAR98D Step003 Open

LNZAR98D Step004 Open

LNZAR98D Closed

LNZAR50D Step001 Closed

LNZAR50D Step002 Closed

LNZAR50D Step003 Closed

LNZAR50D Step004 Closed

LNZAR50D Step005 Closed

LNZAR50D Open

LNZAR50D Closed


Second sheet comes as:


Component Status

LNZAR98D Open

LNZAR50D #VALUE!


Formula used-=INDEX(Sheet1!$D$2:$D$30,LARGE((Sheet1!$A$2:$A$30=A2)*ROW(Sheet1!$A$2:$A$30)-1,1)).


so is there any way to sort this problem?


regards

vaibhav
 
Hi vaibhav tandon,


Certianly it could be done with VBA but unfortunately i don't know that. You may ask some other member who have programming background for the VBA based solution.


..and regarding your second post i am not encountering any such problem like not updating status see this file that i worked out.


http://dl.dropbox.com/u/60644346/vaibhav%20tandon.xlsx


Regards,

Faseeh
 
Faseeh,


Please ignore my last post,Its working fine.Was wondering if formula could be changed to match the below condition:


Suppose if any of the status for one component is "Open" then display the status in second sheet as Open,Irrespective of last status as "Closed" for an individual component?


Component Name Description Status

LNZAR98D Step001 Open

LNZAR98D Step002 Open

LNZAR98D Step003 Open

LNZAR98D Step004 Open

LNZAR98D Closed


So the above data should give "Open" in the second sheet.Is this possible?


Thanks in advance.


Regards

Vaibhav
 
You mean to say that you don't want the last status to be reported. If any of the status contain open, then it should give open rather then closed.
 
I meant that last post was put in a hurry and your formula was running absolutely fine.Pardon me for that post.But could the formula be a bit modified to suit the condition:


Suppose if any of the status for one component is "Open" then display the status in second sheet as Open,Irrespective of last status as "Closed" for an individual component?


Component Name Description Status

LNZAR98D Step001 Open

LNZAR98D Step002 Open

LNZAR98D Step003 Open

LNZAR98D Step004 Open

LNZAR98D Closed


So the above data should give "Open" in the second sheet.Is this possible?


Regards

Vaibhav
 
Dear,


No need for apologies, enter this one in Sheet2, B2 & B3 in my sample file and press Ctrl+Shift+Enter:


Code: 
=IF(SUMPRODUCT((Sheet1!$A$2:$A$14=A2)*(Sheet1!$D$2:$D$14="Open"))>=1,"Open","Closed")


Hope that works!


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