realtoast
New Member
- Joined
- Nov 24, 2015
- Messages
- 47
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
I have a lengthy process that ends in a dynamic, single cell spill array (shown below). This array currently reports all records from a data table. However, I would like to exclude rows where certain cells are blank. I'm feeling like =FILTER() is the right tool, I'm just not able to configure it properly.
There are a couple thousand rows in my data. The columns where if all three values are blank, I'd like the row excluded are: "Vision Coverage", "Dental Coverage", and "Medical Coverage". Or, if you prefer, the columns are AM, AG, and BJ, respectively.
The spill array formula is below. This formula works as intended. So I only need to learn how to wrap FILTER() around it, with the above parameters, to exclude rows with the three blank values.
=LET(
b,"",pid,StartTable[Participant ID],clientNum,"35574",clientName,StartTable[Payroll Profile],
eeFn,StartTable[FirstName],eeMn,IF(StartTable[MiddleName]=""," ",StartTable[MiddleName]),eeLn,StartTable[LastName],eeGender,StartTable[Gender],
eeDOB,StartTable[DOB],eeDOH,StartTable[DOH],addr1,StartTable[Address1],addr2,IF(StartTable[Address2]="","",StartTable[Address2]),city,StartTable[City],state,StartTable[State],zip,StartTable[Zip],country,"USA",status,StartTable[DOL Status],ssn,StartTable[SS Number],ein,StartTable[Employee Code],medPlan,StartTable[Med Plan],denPlan,IF(StartTable[Dental Plan]="","",StartTable[Dental Plan]),visPlan,IF(StartTable[Vision Plan]="","",StartTable[Vision Plan]),depFN,StartTable[FirstName],depMn,StartTable[MiddleName],depLn,StartTable[LastName],depGender,StartTable[Gender],depDOB,StartTable[Birth Date],depSSN,IF(pid="D",StartTable[SSNumber],""),rel,StartTable[[Relationship]],depMemberNum,StartTable[depSubID],subIDType,IF(pid="D","S",""),subSSN,StartTable[subSSN],map,CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38},pid,clientNum,clientName,eeFn,eeMn,eeLn,eeGender,eeDOB,eeDOH,addr1,addr2,city,state,zip,country,b,b,status,b,b,b,b,b,b,b,b,depSSN,ein,b,medPlan,denPlan,visPlan,b,depMemberNum,b,rel,subIDType,subSSN),
map)
Thank you.
There are a couple thousand rows in my data. The columns where if all three values are blank, I'd like the row excluded are: "Vision Coverage", "Dental Coverage", and "Medical Coverage". Or, if you prefer, the columns are AM, AG, and BJ, respectively.
The spill array formula is below. This formula works as intended. So I only need to learn how to wrap FILTER() around it, with the above parameters, to exclude rows with the three blank values.
=LET(
b,"",pid,StartTable[Participant ID],clientNum,"35574",clientName,StartTable[Payroll Profile],
eeFn,StartTable[FirstName],eeMn,IF(StartTable[MiddleName]=""," ",StartTable[MiddleName]),eeLn,StartTable[LastName],eeGender,StartTable[Gender],
eeDOB,StartTable[DOB],eeDOH,StartTable[DOH],addr1,StartTable[Address1],addr2,IF(StartTable[Address2]="","",StartTable[Address2]),city,StartTable[City],state,StartTable[State],zip,StartTable[Zip],country,"USA",status,StartTable[DOL Status],ssn,StartTable[SS Number],ein,StartTable[Employee Code],medPlan,StartTable[Med Plan],denPlan,IF(StartTable[Dental Plan]="","",StartTable[Dental Plan]),visPlan,IF(StartTable[Vision Plan]="","",StartTable[Vision Plan]),depFN,StartTable[FirstName],depMn,StartTable[MiddleName],depLn,StartTable[LastName],depGender,StartTable[Gender],depDOB,StartTable[Birth Date],depSSN,IF(pid="D",StartTable[SSNumber],""),rel,StartTable[[Relationship]],depMemberNum,StartTable[depSubID],subIDType,IF(pid="D","S",""),subSSN,StartTable[subSSN],map,CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38},pid,clientNum,clientName,eeFn,eeMn,eeLn,eeGender,eeDOB,eeDOH,addr1,addr2,city,state,zip,country,b,b,status,b,b,b,b,b,b,b,b,depSSN,ein,b,medPlan,denPlan,visPlan,b,depMemberNum,b,rel,subIDType,subSSN),
map)
Thank you.
