How Do I Filter Spreadsheet Data Based On Another Column?-CodePudding

I am trying to filter out a spreadsheet that will be used for tracking commission payments. There are 3 milestone payments per customer. I am trying to build a separate section that only includes the customer's first and last name, as well as any milestone payment that has a pay date in the column next to it.

I built a spreadsheet to help demonstrate what I am trying to do with some examples of my filters that I am failing at.

Could a spreadsheet wizard take a look and see if what I am trying to accomplish is even possible?

Either EXCEL or Google Sheets is acceptable.

Link to test sheet:

Thank you!

CodePudding user response：

try:

=ARRAYFORMULA(QUERY({IF(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(MONTH(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=10, 1, )),,9^9)))="",, A35:B1000), IF(MONTH(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=10,
 FILTER(A35:1000, REGEXMATCH(A34:34, "Milestone. ")), )}, 
 "where Col1 is not null"))

update

to include year use:

=ARRAYFORMULA(QUERY({IF(TRIM(FLATTEN(QUERY(TRANSPOSE(IF((MONTH(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=10)*(YEAR(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=2021), 1, )),,9^9)))="",, A35:B1000), IF((MONTH(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=10)*(YEAR(
 FILTER(A35:1000, REGEXMATCH(A34:34, "Pay Date. ")))=2021),
 FILTER(A35:1000, REGEXMATCH(A34:34, "Milestone. ")), )}, 
 "where Col1 is not null"))