Create a list from earliest to latest dates based on another cells value

[surkdidat]

Good morning

I have a formula

SMALL(Master!$O$4:$O$275,P41)

- What I am looking to do is have it based on Column V - so if V is empty I want it to include it, however, if it has a value, I want it NOT to be included in the list

Many thanks

 

[steve the fish]

Enter this CTRL-SHIFT-ENTER not just enter:

=SMALL(IF(Master!$V$4:$V$275<>"",Master!$O$4:$O$275),P41)

 

[surkdidat]

Lovely - thank you!

Enter this CTRL-SHIFT-ENTER not just enter:

=SMALL(IF(Master!$V$4:$V$275<>"",Master!$O$4:$O$275),P41)

 

[surkdidat]

Thank you for this. Now I need the lookup to do the same, and omit anything with a value in column .

=INDEX(Master!A4:A400,MATCH('BI STATS'!T40,Master!O4:O400,0))

Enter this CTRL-SHIFT-ENTER not just enter:

=SMALL(IF(Master!$V$4:$V$275<>"",Master!$O$4:$O$275),P41)

 

[Aladin Akyurek]

What is the value 'BI STATS'!T40 contains?

 

[surkdidat]

Sorry, the first formula is in BI STATS T40 (which is a date)

What is the value 'BI STATS'!T40 contains?

 

[Aladin Akyurek]

Sorry, the first formula is in BI STATS T40 (which is a date)

The IF bit

IF(Master!$V$4:$V$275<>"",Master!$O$4:$O$275)

in

=SMALL(IF(Master!$V$4:$V$275<>"",Master!$O$4:$O$275),P41)

all values from the O range which corresponds to a non-empty V cell. Although you are approving this, it is actually the opposite of what you seem saying in your original post.

You now forward:

=INDEX(Master!A4:A400,MATCH('BI STATS'!T40,Master!O4:O400,0))

and comment: "Now I need the lookup to do the same, and omit anything with a value in column." Care to elaborate what does this comment mean?

 

[surkdidat]

Hi - apologies for the delay.. Been off from work and first day back in.... I have reworked formula, and it now works, HOWEVER, as there are multiple date entries (ie I have 3 values with 31 July) - it will only list the first one, but three times, - how can it be amended so it will show all 3 lines where it occurs?

[COLOR=#343A41][FONT="Segoe UI WestEuropean"]=SMALL(IF([/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"XXXXX - Completed")*[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"XXXXX Work In Progress")*[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"XXXXX Rejected")*[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"")*[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"YYYYY Rejected")*[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"](Master!$Q$4:$Q$275<>"YYYYY Completed"),[/FONT][/COLOR]
[COLOR=#343A41][FONT="Segoe UI WestEuropean"]Master!$O$4:$O$275),W9)[/FONT][/COLOR]

 

[Aladin Akyurek]

Care to word what the formula of #8 is meant to do?

 

[surkdidat]

Hi there. Thank you for your help. I have completely reworked from scratch using RANK what I was trying to achieve, and it now works