return reference if in date period and 'yes'

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
So I have a table with a list of loads of different references with dates assigned to them and also 'yes' or 'no' assigned to them. I need to return the reference numbers if the date is in a specific period and they have 'yes' assigned.

Example
DateRefStatusJan-Mar 2019 & Yes
01/01/19#111Yes#111
23/01/19#444Yes#444
25/02/19#333No#222
01/01/19#222Yes#555
07/03/19#555Yes

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Similar concept to this one I helped you with. :)

Excel Workbook
ABCDEFGH
1DateRefStatusStart Month1List
21/01/2019#111YesEnd Month3#111
323/01/2019#444YesStatusYes#444
425/02/2019#333No#222
51/01/2019#222Yes#555
67/03/2019#555Yes
Date Yes
 
Upvote 0
Hi Peter, thanks for the help on this once again. Does this method work even with columns A, B and C in a different sheet or workbook?
 
Upvote 0
Give it a try? ;)


Hi peter, I have tried, I've also tried putting the information on the same sheet and its not working. I think it's something to do with the equation inside the aggregate equation? Not sure :(
 
Upvote 0
If you set up a fresh sheet exactly like the one in post 3 does it work?

Simply saying "its not working" gives us nothing to go on, especially when you can see the posted example is working.
When things don't work, you need to explain in what way they don't work (gives #REF ! error, Returns "x" instead of "y", crashes Excel etc) and give an example or two of sample data that it failed for.
 
Last edited:
Upvote 0
Give it a try? ;)

Hello again! I got something to work however now I want multiple status conditions to be picked up
Example

DateRefStatusJan-Mar 19 "Active" & "Reactive" & "Yes"
15/01/19#111Yes#111
14/02/18#222No#333
27/03/19#333Active#444
30/01/19#444Reactive
12/01/19#555No

<tbody>
</tbody>


I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

=INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))


This returns a #NUM ! error
 
Last edited:
Upvote 0
I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

=INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))
There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

Here is one way to do it.

Excel Workbook
ABCDEFGH
1DateRefStatusList
215/01/2019#111Yes#111
314/02/2018#222No#333
427/03/2019#333Active#444
530/01/2019#444Reactive
612/01/2019#555No
7
Date Yes (2)
 
Upvote 0
There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

Here is one way to do it.

Date Yes (2)

ABCDEFGH
1DateRefStatus List
215/01/2019#111Yes #111
314/02/2018#222No #333
427/03/2019#333Active #444
530/01/2019#444Reactive
612/01/2019#555No
7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:47px;"><col style="width:57px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
H2=IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


You clever clever man! Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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