Filter function condition ideas?

[MountainFog]

Hi All,

I can't use the xl2bb tool due to work policy. I have a filter function that I am using to filter a list of timesheet data, but when a correction is made, it doesn't delete the entry, but cancels it out with another entry. I'm trying to use a filter function with array formula to filter the data down to people that have overhead hours, with entries that don't have a canceled-out entry. I'm able to filter to overhead, but I'm stuck when it comes to the other condition. Essentially, I want something that says that the sum of that person, for that date, does not equal 0. Not sure how to do that as an array formula that would spill down. My initial reaction was Sumifs, but not sure how to convert that into array, particularly the condition.

=LET(a,$AT:$AY,FILTER(a,(CHOOSECOLS(a,1)<>"")*(CHOOSECOLS(a,1)<>"Task")*($AZ:$AZ="Overhead")))

-------------AT--------------------------------------------------------AU-------------------------AV-----------------------AW-------------------------------AX--------------------------------AY----------------------------AZ

Task	LaborCategory	PayCode	Person	Date	Hours	Helper
xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23​	8​	Overhead
xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23​	-8​	Overhead
xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26​	8​	Labor
xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26​	-8​	Labor
xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	4-Jul-29​	8​	Overhead

Thank you all for the help!

 

[KRice]

How do you make a distinction between "overhead" and "labor" in column AZ, as the Task column uses both of those words? The LaborCategory column does not...is that column used to create the AZ helper column?

 

[KRice]

Here is one idea. There may be some better ways, but seems to work.

MrExcel_20240203.xlsx
	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1	Task	LaborCategory	PayCode	Person	Date	Hours		Overhead NOT Canceled Out
2	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23	8		Doe, John	4-Jul-26	8
3	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23	-8		Adams, John	4-Sep-29	8
4	xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26	8		Abby, Chris	4-Oct-29	8
5	xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26	-8
6	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	4-Jul-26	8
7	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	5-Aug-26	8
8	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	5-Aug-26	-8
9	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	4-Sep-29	8
10	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	5-Jun-26	8
11	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	5-Jun-26	-8
12	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	4-Oct-29	8
Sheet3

Cell Formulas
Range	Formula
BA2:BC4	BA2	=LET(a,$AT:$AY,lrow,MAX(IF(ISBLANK(INDEX(a,,1)),0,ROW(INDEX(a,,1)))),data,DROP(TAKE(a,lrow),1),oh,ISNUMBER(SEARCH("Overhead",INDEX(data,,2))),fdata,FILTER(data,oh*(INDEX(data,,1)<>"")),pd,UNIQUE(CHOOSECOLS(fdata,4,5)),res,BYROW(pd,LAMBDA(r,SUM(FILTER(INDEX(fdata,,6),(INDEX(fdata,,4)=INDEX(r,1))*(INDEX(fdata,,5)=INDEX(r,2)))))),FILTER(HSTACK(pd,res),res>0))
Dynamic array formulas.

 

[KRice]

Here is a slightly shortened version based on the same ideas, using the following steps:

1. Define "a", the column range containing the source data.
2. Define a filtered data set, called "fdata", by filtering "a" and keeping only rows where the 1st column of "a" is not empty and the 2nd column of "a" contains the word "Overhead". That should identify only Overhead entries (I think?), while eliminating blank rows and the column headings...essentially creating a consolidated array containing only data of interest.
3. Define a two-column array, called "pd" consisting of only unique combinations of Person and Date found in "fdata".
4. Create a one-column array of sums, called "res", containing the sum of Hours for each combination Person/Date in the "pd" array. This is done using the BYROW LAMBDA function, which passes each row of "pd", one at a time, to the FILTER function, where the 6th column of "fdata" (Hours) is filtered based on whether the 4th column of "fdata" (Person) matches the 1st element of the "pd" row (Person) AND whether the 5th column of "fdata" (Date) matches the 2nd element of the "pd" row (Date). The resulting filtered array of hours is then summed, producing a result for that particular row of "pd", and then repeated until all rows of "pd" have been processed.
5. Finally, the "pd" array and the resulting sums array "res" are stacked together horizontally and filtered to include only those where the summed hours "res" are greater than 0, meaning the Overhead hours were not cancelled out entirely.

MrExcel_20240203.xlsx
	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1	Task	LaborCategory	PayCode	Person	Date	Hours		Overhead NOT Canceled Out
2	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23	8		Doe, John	4-Jul-26	8
3	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	8-May-23	-8		Adams, John	4-Sep-29	8
4	xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26	8		Abby, Chris	4-Oct-29	8
5	xxxxx-Overhead Labor	xxxxx-Labor	REG	Doe, John	5-Jun-26	-8		Falls, Ramey	5-Nov-26	8
6	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Doe, John	4-Jul-26	8
7	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	5-Aug-26	8
8	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	5-Aug-26	-8
9	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Adams, John	4-Sep-29	8
10	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	5-Jun-26	8
11	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	5-Jun-26	-8
12	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Abby, Chris	4-Oct-29	8
13
14	xxxxx-Overhead Labor	xxxxx-Overhead Labor	REG	Falls, Ramey	5-Nov-26	8
Sheet3

Cell Formulas
Range	Formula
BA2:BC5	BA2	=LET(a,$AT:$AY, fdata,FILTER(a,ISNUMBER(SEARCH("Overhead",INDEX(a,,2)))*(INDEX(a,,1)<>"")), pd,UNIQUE(CHOOSECOLS(fdata,4,5)), res,BYROW(pd,LAMBDA(r,SUM(FILTER(INDEX(fdata,,6),(INDEX(fdata,,4)=INDEX(r,1))*(INDEX(fdata,,5)=INDEX(r,2)))))), FILTER(HSTACK(pd,res),res>0))
Dynamic array formulas.

 

[MountainFog]

Thank you! I stumbled on the only solution, which is HSTACK. Thank you!

 

[KRice]

You're welcome....happy to help.