return reference if in date period and 'yes'

[mattbnorris]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Ref[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan-Mar 2019 & Yes[/TD]
[/TR]
[TR]
[TD]01/01/19[/TD]
[TD]#111[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#111[/TD]
[/TR]
[TR]
[TD]23/01/19[/TD]
[TD]#444[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#444[/TD]
[/TR]
[TR]
[TD]25/02/19[/TD]
[TD]#333[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]#222[/TD]
[/TR]
[TR]
[TD]01/01/19[/TD]
[TD]#222[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]#555[/TD]
[/TR]
[TR]
[TD]07/03/19[/TD]
[TD]#555[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

You clever clever man! Thank you so much!

You're very welcome. Thanks for the kind words.

 

[mattbnorris]

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)

	A	B	C	D	E	F	G	H
Ref	Status					List
#111	Yes					#111
#222	No					#333
#333	Active					#444
#444	Reactive
#555	No

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Date[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]15/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]14/02/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]27/03/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]30/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]12/01/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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

Hi Peter,

Currently revisiting this query

I now want to create this into one big list of with different date periods after each other in the list. So i've started with a nested if statement where i'm using your formula (as quoted above) based on how many records i've found in a date range. For example...

[TABLE="width: 500"]
<tbody>[TR]
[TD]IDs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1 (Jan-Mar)[/TD]
[TD]Q2 (Apr-Jun)[/TD]
[TD]Q3 (Jul-Sep)[/TD]
[TD]Q4 (Oct-Dec)[/TD]
[/TR]
[TR]
[TD]#111[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#333[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#444[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#777[/TD]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#999[/TD]
[TD]Jun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Okay under the quarter headings i'm counting the amount of times it finds a record in that time period and then to get the list i'm using a nested IF function with the formula previously provided

=IF($E$4+2=ROW(A1),IF($E$4+$F$4+2=ROW(A1),[...continued...],Aggregate_Data_Formula(01/04/19 - 30/06/19)),Aggregate_Data_Formula(01/01/19 - 31/03/19))

Dragging this down works until it gets to the changing point - so in my example above it returns the first 3 values that are part of Q1 but then won't return further values. The issue is due to the ROW formula in previously provided formula:

=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))),"")

If i'm nesting the formulas in IF statements as I propose then the ROW part at the end of formula above needs to also include the reference to the Q1 count; as in it would need to start at ROWS(A$5:A5) in order to provide the right IDs.

I know that's long winded, apologies if you don't understand.

Thanks,
Matt

 

[Peter_SSs]

Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values

 

[mattbnorris]

Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values

It was just a quick notation for the table on here, in my actual sheet I have actual dates. But its fine, I managed to figure my problem out actually...


=ROW(INDIRECT(E$4+F$4+2&":A"ROW(A1))

and dragging this down allows me to get the required outcome.

Thanks