Formulae problem

[EasyM84]

Hi

I have a formula which currently checks a list of dates and returns a figure provided certain conditions are met.

The formula I am using is the following one:

=IF(ROWS(E$9:E9)>$G$4,"",INDEX(A$2:A$728,SMALL(IF($A$2:$A$728>=$E$4,IF($A$2:$A$728<=$F$4,ROW($A$2:$A$728)-ROW($A$2)+1)),ROWS(E$9:E9))))

I would like excel to ignore a particular result/date if that particular result/date appears in another column. In that situation, I would like Excel to continue with the instruction ignoring that particular result.

Are you able to suggest how I can amend the above formula to achieve that?

My knowledge of Excel is intermediate so please bear with me.

Thank you in advance for any help you can give me.

Kind regards,

Marcelo

 

[FDibbins]

maybe this (untested)...
=IF(ROWS(E$9:E9)>$G$4,"",INDEX(A:A,SMALL(IF($A$2:$A$728>=$E$4)*($A$2:$A$728<=$F$4)*(newrange<>newcriteria),ROW($A$2:$A$728))),ROWS(E$9:E9))))

 

[EasyM84]

maybe this (untested)...
=IF(ROWS(E$9:E9)>$G$4,"",INDEX(A:A,SMALL(IF($A$2:$A$728>=$E$4)*($A$2:$A$728<=$F$4)*(newrange<>newcriteria),ROW($A$2:$A$728))),ROWS(E$9:E9))))

I am afraid it is not working. As written it says it has too many brackets but when removed the brackets it returns "N/A".

Can I just check that for (newrange<>newcriteria) I should use ($A$2:$A$728<>I:I) so that in addition excel also checks that the date picked is not on the list at column I?

Many thanks

M

 

[FDibbins]

OK before you include the new part, see if my modification works OK
=IF(ROWS(E$9:E9)>$G$4,"",INDEX(A:A,SMALL(IF($A$2:$A$728>=$E$4)*($A$2:$A$728<=$F$4),ROW($A$2:$A$728)),ROWS(E$9:E9))))

Then, if you are testing to see if the value in A (say, A2) is to be found anywhere in I, this should work...
=IF(ROWS(E$9:E9)>$G$4,"",INDEX(A:A,SMALL(IF($A$2:$A$728>=$E$4)*($A$2:$A$728<=$F$4)*($I$2:$I$728=$A2),ROW($A$2:$A$728)),ROWS(E$9:E9))))

 

[EasyM84]

Hi FDibbins,

Thank you for the further post. I am afraid it hasn't worked so I thought I would share an exact copy of my document with you if that's ok? Link below. Please feel free to amend/delete anything in it.

https://docs.google.com/spreadsheets/d/1g4iN5TT62j9q16vOyOWGGfBvjrpAUQaok6TXdOY2Az0/edit?usp=sharing

Column A has the list of all available dates, past and future.

Column G7 to G27 are the ones subject to the conditions. In essence I want the results in G7:G27 to display all future appointments from Column A unless any of those results are in column I. In that case, I want that particular result ignored.

At the moment, G7:G27 only shows future appointments but does not ignore those results on column I.

Sorry for the additional hassle but I was hoping to better explain what I am trying to achieve by sharing my work.

Thank you in advance for your input.

Kind regards,

M

 

[EasyM84]

Hi

Sorry to post again but I was wondering if you had any further suggestions after seeing the formula I posted? I am at a loss and desperate for ideas!

Thanks

M

 

[Marcelo Branco]

See if this does what you need

Array formula in G8 copied down
=IF(ROWS(G$8:G8)>$G$4-COUNT($I$2:$I$5),"",INDEX(A$2:A$728,SMALL(IF($A$2:$A$728>=$E$4,IF($A$2:$A$728<=$F$4,IF(ISNA(MATCH($A$2:$A$278,$I$2:$I$5,0)),ROW($A$2:$A$728)-ROW($A$2)+1))),ROWS(G$8:G8))))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

The formula above need a small adjustment to handle the case that some dates to be ignored (I2:I6) are not inside the date range in E4:F4

New version
=IF(ROWS(G$8:G8)>$G$4-COUNTIFS($I$2:$I$6,">="&$E$4,$I$2:$I$6,"<="&$F$4),"",INDEX(A$2:A$728,SMALL(IF($A$2:$A$728>=$E$4,IF($A$2:$A$728<=$F$4,IF(ISNA(MATCH($A$2:$A$278,$I$2:$I$5,0)),ROW($A$2:$A$728)-ROW($A$2)+1))),ROWS(G$8:G8))))
Ctrl+Shift+Enter

M.

 

[EasyM84]

See if this does what you need

Array formula in G8 copied down
=IF(ROWS(G$8:G8)>$G$4-COUNT($I$2:$I$5),"",INDEX(A$2:A$728,SMALL(IF($A$2:$A$728>=$E$4,IF($A$2:$A$728<=$F$4,IF(ISNA(MATCH($A$2:$A$278,$I$2:$I$5,0)),ROW($A$2:$A$728)-ROW($A$2)+1))),ROWS(G$8:G8))))
Ctrl+Shift+Enter

M.

You are out of this world! Thank you. It worked. I bow to your knowledge!

M

 

[Marcelo Branco]

You are welcome. Glad to help

M.
ps: use the formula in post 8