Custom Function required for multiple conditions

[Rajender]

Hi All,

I have a Master Table Worksheet. I want to print message in Column DX "to be check" if below mentioned conditions matches.

1) RESOURCE STATUS = Should be ENGAGED/APPROVED.
2) ASSIGNMENT STATUS = Should be Blank
3) Now check for month from cell CW to R any value written as zero but the previous cell month always greater than "0".
4) Now check that header month if it is equal to month of column name "END DATE" or not ?, if not it should be marked as "to be check" if month matched "ok" and for rest cases "Not Applicable".

Note : Header showing as month in the table is "Text field"

Below is example :-
Example 1

1) if RESOURCE STATUS = LEFT PROJECT & ASSIGNMENT STATUS = OLD-INACTIVE than output in DX column should be "Not Applicable"
2) if RESOURCE STATUS = Engaged Approved & ASSIGNMENT STATUS = Blank and End Date is written as "31-05-2018' now start check from column CW and found where "0" started in coloumn after and greater than 0 value.
in below example 0 showing in month of May-14(its header name and in Text). Now I need to check the month year "May-14 " with Date written in column End date column which is "31-05-2018". and its not matching here.
So I need the output as "to be check".

and if in case we have End date was 31-May-14 for below mentioned same case than output should be "ok".
Jan-14Feb-14Mar-14Apr-14May-14
00110


Below is the link to original sample file as well.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gTeORe9jTWmmrlb3

Thanks in advance for the valuable suggestions and support.

Regards,
Rajender

 

[Chihiro]

... Couple of suggestions.
1. Don't ever use Multi-line column header. It just makes structured reference difficult to manage and defeats the purpose of having data in a table. If you need to, use wrap text, but do not use carriage return (ALT + ENTER).

2. Avoid verbose column names. Keep it succinct and alpha-numeric only.

 

[Rajender]

Hi Chihiro,

Well noted your comments, but actually this template is very old created by someone else and around 35 macros are taking references only from this template only. its very difficult for me to change the existing macros. because I have only knowledge of basic VBA macro of recording with little adjustments.

That's why I am looking for solution if any custom function can be created or by using any helping columns I can get this result ?

Regards,
Rajender

 

[Rajender]

Hi Chihiro,

I found one formula nearest to my requirement could you help me out , what modification I need to require here :-
Here its giving me the output and capture header of last non-zero cell.
but I need here column header next to last non zero cell.

=LOOKUP(2,1/(R2:CW2<>0),$R$1:$CW$1)

I tried to change this formula to as below as well.
But in this case its returning false value if all the headers are filled from column R to CW.
=LOOKUP(2,1/(R2:CW2<>0),$S$1:$CX$1)

Please suggest , what modification I need to done here to get the next column header.

Regards,
Rajender

 

[Chihiro]

Try...
=IF((F2="ENGAGED/APPROVED")*(G2=""),INDEX($R$1:$CW$1,,LOOKUP(2,1/(R2:CW2<>0),COLUMN($R$1:$CW$1))+1),"N/A")

 

[Rajender]

Hi Chihiro,

Thanks for your help. I have checked the given formula , but its not showing the correct Header output.
I have shown the correct output in column DX in attached file.
Please suggest , where I need to do modification.

 

[Chihiro]

Ah I misread your first criteria.

Let me review it.

 

[Chihiro]

Hmm, why is row 27 given date, while row 29 is left as "N/A"?

If both should be filled. Then use...
=IF((F31="ENGAGED/APPROVED"),INDEX($R$1:$CW$1,,LOOKUP(2,1/(R31:CW31<>0),COLUMN($R$1:$CW$1)-17)+1),"N/A")

 

[Rajender]

Hi Chihiro,

Still the output is not correct , if there is no Zero in the row, its still showing output as last header row.

In my last attachment uploaded here , I have updated the correct output file.
Please assist.

Regards,
Rajender

 

[Chihiro]

Can't you just nest everything in IF condition?

Like..
=IF(COUNTIF(R2:CW2,0)=0,"N/A",previous formula)

FYI - Generally speaking we recommend that you upload file with just enough data to demonstrate your issue. When there's a lot of noise in data, many of us will gloss over the detail and may miss what you are really after.

 

[p45cal]

See attached.
Column DY (I've called it ColumnNo) is the linchpin and returns a column number if conditions 1, 2 and 3 are fulfilled. It's a long formula and should be array-entered:

=MAX((ASSIGNMENTS[@[Jan-14]:[Nov-20]]>0)*(ASSIGNMENTS[@[Feb-14]:[Dec-20]]=0)*NOT(ISBLANK(ASSIGNMENTS[@[Feb-14]:[Dec-20]]))*COLUMN(ASSIGNMENTS[@[Feb-14]:[Dec-20]])*ISBLANK([@[ASSIGNMENT STATUS]])*([@[RESOURCE
STATUS]]="ENGAGED/APPROVED"))

Column DZ is not needed but is there to compare with your CorrectOutput2 column. It can be deleted.
Column EA (I've called it Check) is your condition 4. I've left the table fitered on just that column alone. Its formula is (normally entered):

=IFERROR(IF(TEXT([@[END DATE]],"MMM-YY")=INDEX(ASSIGNMENTS[[#Headers],[SIGNUM]:[Dec-20]],[@ColumnNo]),"ok","to be check"),"N/A")

 

[Rajender]

See attached.
Column DY (I've called it ColumnNo) is the linchpin and returns a column number if conditions 1, 2 and 3 are fulfilled. It's a long formula and should be array-entered:

=MAX((ASSIGNMENTS[@[Jan-14]:[Nov-20]]>0)*(ASSIGNMENTS[@[Feb-14]:[Dec-20]]=0)*NOT(ISBLANK(ASSIGNMENTS[@[Feb-14]:[Dec-20]]))*COLUMN(ASSIGNMENTS[@[Feb-14]:[Dec-20]])*ISBLANK([@[ASSIGNMENT STATUS]])*([@[RESOURCE
STATUS]]="ENGAGED/APPROVED"))

Column DZ is not needed but is there to compare with your CorrectOutput2 column. It can be deleted.
Column EA (I've called it Check) is your condition 4. I've left the table fitered on just that column alone. Its formula is (normally entered):

=IFERROR(IF(TEXT([@[END DATE]],"MMM-YY")=INDEX(ASSIGNMENTS[[#Headers],[SIGNUM]:[Dec-20]],[@ColumnNo]),"ok","to be check"),"N/A")

Hi Chihiro,

Thanks a lot for your valuable support to solve my issue.
Its working fine now. Have a nice day to you.

Regards,
Rajender