0

I have a sheet with multiple date columns determining the lifecycle of a product.

How do I filter all the columns, so I can see on which Month the lifecycles of products are at?

e.g. I filter by month of "July", I can see the product of "X" has got a Testing date falling July

enter image description here

So in the screenshot, typing in "May" should show products A, B and E because they have checkpoints that occur in May (First Review and Second Review)

Note: needs to be the month of the calendar year, so an edge case is we don't want to see months of previous or future years?

We tried using an advanced filter with the MONTH function but that didn't work.

Any suggestions?

0

Name the cell you want to type the filter month into filter_month.

You can then use this formula in a new column in your table:

=OR(TEXT(B2:E2,"mmmm")=filter_month)

If you drag it down, this will create a column of TRUE or FALSE. You can filter on TRUE to get the rows you want.

This works because the TEXT function used with a range here will create an array of four items - each of the months, full-length, of each of the columns B through E. Then, comparing each of those to filter_month will create an array of four items, containing TRUE or FALSE, depending on whether the month from that column matches the text typed into filter_month. Then, the OR function will just say, if any single one of the four items is TRUE, then return TRUE.

enter image description here

0

An array (CSE) formula wrapped with IFERROR solves the issue.

enter image description here

  • An array (CSE) Formula in cell I91:

    {=IFERROR(INDEX(A$91:A$95,SMALL(IF(MONTH($B$91:$C$95)=5,ROW($A$91:$A$95)-MIN(ROW($A$91:$A$95))+1),ROWS($H$98:H98))),"")}
    

N.B.

  • SMALL(IF(MONTH($B$91:$C$95)=5, here 5 is month's number for May, and is editable also.

  • You may replace 5 with cell reference in the formula, then it will be like for example, SMALL(IF(MONTH($B$91:$C$95)=$G$90, here G90 has month's number.

  • Finish formula with Ctrl+Shift+Enter and fill across.

  • Adjust cell references in the formula as needed.

1
  • @tomaytotomato,, yes it's possible,,, if U find that my post is the solution you were looking for then you may accept it as an Answer as well Up vote too. ☺ May 26 '20 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.