How Do I Filter Spreadsheet Data Based On Another Column?

user_12155983 0
Viewed 54

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: Link to spreadsheet

Screenshot of spreadsheet

Thank you!

google-sheets-formula spreadsheet google-sheets excel-formula excel
1 Answers

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

enter image description here

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"))
user_56326290
Related