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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
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

<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


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...

IDsQ1 (Jan-Mar)Q2 (Apr-Jun)Q3 (Jul-Sep)Q4 (Oct-Dec)
#111Jan3210
#333Mar
#444Jan
#777Apr
#999Jun

<tbody>
</tbody>


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
 
Upvote 0
Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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