Search code examples
google-sheetsgoogle-sheets-formula

Filtering Google Sheet with Merged-Celled Headers & Extract Data

I have trouble filtering and getting the right data with my Google Sheet due to merged-celled headers. I can't use the basic filtering tool. I think there's a way to filter it using functions such as QUERY and Array but I just don't know the right formula. I've been watching Youtube tutorials and I still haven't figured it out yet.

If you had this spreadsheet, and you want to see only the list of members who has missing payments let's say for the month of April. How would you filter it? Is it possible? enter image description here

Also, I want too auto extract all received reimbursement amount for all the completed sessions of the members in the list. This reimbursement amount can be found in two separate tab sheets RR1 and RR2. In sheets RR1 and RR2Is there's only specific member ID and month, no name, and it's put in 1 cell. Is it possible to auto-extract the corresponding total reimbursement amount and populate it in the Received Reimbursement column of the main sheet Tracker? What function and formula would you use?

enter image description here

I hope this all makes sense. Thank you so much for assistance.

Googlesheet: https://docs.google.com/spreadsheets/d/1q_B5Cj_vv9Jzcm9aLAY_gNKHaaoEEUrJ/edit?usp=sharing&ouid=115853268191643677574&rtpof=true&sd=true

Solution

  • Try this formula in Cell_Q3 for April month. For the other months just copy the Q3 Cell with formula & paste it in H3, K3, N3, T3... for their respective months to populate

    =map($A3:$A,lambda(Σ,if(Σ="",,let(x,{'RR1'!$A:$A;'RR2'!$A:$A},y,{'RR1'!$B:$B;'RR2'!$B:$B},ifna(sum(filter(y,--regexextract(x,"\d+$")=Σ,regexmatch(x,"^(?:ASH|Renew) "&P1&" 2023"))))))))

    enter image description here

    • Regarding filtering the data; you can skip the first row (merged cells) and apply from the second row to the bottom