Formulae problem

EasyM84

New Member
Joined
Oct 15, 2017
Messages
5
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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))))
 
Upvote 0
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
 
Upvote 0
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))))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top