1

The following formula produces a list of results in a single cell as expected.

=BYROW(FILTER(M4#,{1,0}),
    LAMBDA(a,
        LET(
                 varARR, SORT(UNIQUE(FILTER(FILTER(rngGapAwarded,((rngAwardedLCAT=a)*((rngEmployeeNameAwarded="TBD")+(rngEmployeeNameAwarded="")))),{0,0,0,1,1,0,0,1,0,1,1}),FALSE),4),

                 TEXTJOIN(CHAR(10),,
                     INDEX(varARR,,1)&"  "&
                     INDEX(varARR,,2)&"  "&
                     INDEX(varARR,,3)&"  POP: "&
                     TEXT(INDEX(varARR,,4),"m/d/yy")&" -- "&
                     TEXT(INDEX(varARR,,5),"m/d/yy")
                 )
           )
     )
)

What I desire is to add a COUNT to each "row" of the result; if the UNIQUE result consisted of 5 "records" for the first row, and 2 "records" for the second row, the displayed value would be:

Column A Column B
SomeLCAT 5 instances of PROJECT CODE LOCATION POP m/d/yy -- m/d/y &CHAR(10) & 2 instances of PROJECT2 CODE2 LOCATION2 POP m/d/yy -- m/d/y
AnotherLCAT AnotherLCAT Data

The results of the above formula, currently are

SomeLCAT PROJECT CODE LOCATION POP m/d/yy -- m/d/y PROJECT2 CODE2 LOCATION2 POP m/d/yy -- m/d/y

In another column I have a formula which provides the TOTAL COUNT using

=BYROW($H4#,LAMBDA(a, IFERROR(ROWS(FILTER(rngGapAwarded,((rngAwardedLCAT=a)*((rngEmployeeNameAwarded="TBD")+(rngEmployeeNameAwarded=""))))),0)))

But I cannot figure out how to add a count of instances to my formula above so that I get the details.

Randy

@JvdV: Current and Desired Result

Sample Data: enter image description here

Data Table:

LCAT Employee Emp ID # Project Description Task Div Travel (Y/N) Location SY POP Start POP End
PROGRAM MANAGER Emp_Name_#1 1 Progress Road Multiple 2 N Progress Rd 3/21/2022 4/4/2023
ADMINISTRATIVE ASSISTANT TBD Not Avail Progress Road Multiple 2 N Progress Rd 4/12/2021 4/4/2023
ELECTRONICS TECHNICIAN II TBD Not Avail Progress Road Multiple 2 N Progress Rd 2/6/2023 4/4/2023
ELECTRICIAN II Emp_Name_#4 4 Progress Road Multiple 2 N Progress Rd 4/12/2021 4/4/2023
Program Manager Emp_Name_#1 1 Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Project Manager Emp_Name_#2 2 Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Program Manager Emp_Name_#1 1 Project #3 5127-LAA 2 N Norfolk, VA 3/7/2022 9/20/2024
PROJECT MANAGER Emp_Name_#2 2 Project #3 5127-LAA 2 N Norfolk, VA 3/7/2022 9/20/2024
Program Manager Emp_Name_#1 1 Project #4 5702-LAA 2 N Norfolk, VA 3/7/2021 4/10/2023
TEAM LEADER Emp_Name_#3 3 Project #4 5702-LAA 2 N Norfolk, VA 3/21/2022 4/4/2023
ENGINEERING TECH III Emp_Name_#5 5 Project #4 5702-LAA 2 N Norfolk, VA 3/21/2022 4/4/2023
ENGINEERING TECH III Emp_Name_#13 13 Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 2/6/2023 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 1/31/2023 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 1/31/2023 4/4/2023

Results Table:

Labor Category Total Gaps Projects with Gaps Desired Result in Column(O)
ADMINISTRATIVE ASSISTANT 1 Progress Road Multiple Progress Rd POP: 4/12/21 -- 4/4/23 1 Required for Progress Road Multiple Progress Rd POP: 4/12/21 -- 4/4/23
ELECTRONICS TECHNICIAN II 1 Progress Road Multiple Progress Rd POP: 2/6/23 -- 4/4/23 1 Required for Progress Road Multiple Progress Rd POP: 2/6/23 -- 4/4/23
ENGINEERING TECH III 7 Project #4 5702-LAA Norfolk, VA POP: 4/12/21 -- 4/4/23 Project #4 5702-LAA Norfolk, VA POP: 1/31/23 -- 4/4/23 Project #4 5702-LAA Norfolk, VA POP: 2/6/23 -- 4/4/23 4 Required for Project #4 5702-LAA Norfolk, VA POP: 4/12/21 -- 4/4/23 2 Required for Project #4 5702-LAA Norfolk, VA POP: 1/31/23 -- 4/4/23 1 Required for Project #4 5702-LAA Norfolk, VA POP: 2/6/23 -- 4/4/23
MECHANIC 5 Project #2 5120-LAA Dallas, TX POP: 4/11/22 -- 7/2/22 5 Required for Project #2 5120-LAA Dallas, TX POP: 4/11/22 -- 7/2/22

EDIT FOLLOWING @JvdV Solution:

The formula provided by @JvdV works if there are no empty rows in the range.

=LET(a,A2:K25,b,SORT(FILTER(a,(INDEX(a,,2)="TBD")+(INDEX(a,,2)="")),10),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,FILTER(b,TAKE(b,,1)=e),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))

As noted in my comment, the raw data worksheet utilizes NULL/BLANK rows to separate projects and I do not have the ability to change this as this is a shared workbook. While effort has been made to enforce data validation, my co-workers will find creative ways to break the results. So, I have to find a solution that accounts for this.

After removing the blank rows in the sample data, I did some further "testing" with the following results:

  1. If column(A) is null/blank the formula returns #VALUE at M2. This maybe able to be solved "on-the-fly" by returning "TBD" for blank cells, without changing the source data?
  2. If column(B) is null/blank and the result in column(O) would have multiple values, then the formula returns returns #VALUE at (O#). When (O#) result is a single entry there is no error. Again, this maybe able to be solved "on-the-fly" by returning "TBD" for blank cells, without changing the source data?
  3. If column(J) or (K) is null/blank or NOT a date and the result in column(O) would have multiple values, then the formula returns #VALUE at (O#). With the same conditions, if (O#) result is a single entry, the result displays with the null/blank as a 0 or text (i.e. TBD). In this error case, TBD works for single entry, but fails for multi-entries, so does not seem like an "easy" fix.

I believe solving 1, 2, and 3 will negate the issue with the empty rows in the dataset. Looking in detail at the raw data in the actual workbook, there are many instances of these "issues" but in the context of the data make sense why they exist, I must find a solution that accounts for these and still works.

Additional troubleshooting: Using this data set (note empty row and cells), the results are as expected using the formula shown. This is true on the actual data set unless the conditions of Issue 2 and 3 (above) exist:

=LET(a,rngGapAwarded,b,SORT(FILTER(a,((INDEX(a,,1)<>"")*((INDEX(a,,2)="TBD")+(INDEX(a,,2)="")))),1),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,SORT(FILTER(b,TAKE(b,,1)=e),10),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))
LCAT Employee Emp ID # Project Description Task Div Travel (Y/N) Location SY POP Start POP End Analysis Results
PROGRAM MANAGER Emp_Name_#1 1 Progress Road Multiple 2 N Progress Rd 3/21/2022 4/4/2023 Labor Category Total Gaps Projects with Gaps
ADMINISTRATIVE ASSISTANT TBD Not Avail Progress Road Multiple 2 N Progress Rd 4/12/2021 4/4/2023 ADMINISTRATIVE ASSISTANT 1 1 Required for Progress Road Multiple Progress Rd POP: 04/12/2021 -- 04/04/2023
ELECTRONICS TECHNICIAN II TBD Not Avail Progress Road Multiple 2 N Progress Rd 2/6/2023 4/4/2023 ELECTRONICS TECHNICIAN II 1 1 Required for Progress Road Multiple Progress Rd POP: 02/06/2023 -- 04/04/2023
ELECTRICIAN II Emp_Name_#4 4 Progress Road Multiple 2 N Progress Rd 4/12/2021 4/4/2023 ENGINEERING TECH III 7 3 Required for Project #4 5702-LAA Norfolk, VA POP: 04/12/2021 -- 04/04/2023 2 Required for Project #4 5702-LAA Norfolk, VA POP: 01/31/2023 -- 04/04/2023 2 Required for Project #4 5702-LAA Norfolk, VA POP: 02/06/2023 -- 04/04/2023
Program Manager Emp_Name_#1 1 Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022 Mechanic 5 1 Required for Project #2 5120-LAA Dallas, TX POP: 02/11/2022 -- 07/02/2022 3 Required for Project #2 5120-LAA Dallas, TX POP: 04/11/2022 -- 07/02/2022 1 Required for Project #2 5120-LAA Dallas, TX POP: TBD -- 01/00/1900
Project Manager Emp_Name_#2 2 Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 4/11/2022 7/2/2022
Mechanic TBD Not Avail Project #2 5120-LAA 2 Y Dallas, TX 2/11/2022 7/2/2022
Mechanic Project #2 5120-LAA 2 Y Dallas, TX TBD
Program Manager Emp_Name_#1 1 Project #3 5127-LAA 2 N Norfolk, VA 3/7/2022 9/20/2024
PROJECT MANAGER Emp_Name_#2 2 Project #3 5127-LAA 2 N Norfolk, VA 3/7/2022 9/20/2024
Program Manager Emp_Name_#1 1 Project #4 5702-LAA 2 N Norfolk, VA 3/7/2021 4/10/2023
TEAM LEADER Emp_Name_#3 3 Project #4 5702-LAA 2 N Norfolk, VA 3/21/2022 4/4/2023
ENGINEERING TECH III Emp_Name_#5 5 Project #4 5702-LAA 2 N Norfolk, VA 3/21/2022 4/4/2023
ENGINEERING TECH III Emp_Name_#13 13 Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 2/6/2023 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 2/6/2023 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 1/31/2023 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 4/12/2021 4/4/2023
ENGINEERING TECH III TBD Not Avail Project #4 5702-LAA 2 N Norfolk, VA 1/31/2023 4/4/2023

Using this sample data set, if B20 is "" a #VALUE ERROR is received in O5 only; the rest of the results table works. Further, when there are multiple results to be reported in O, if a Column D, E, H, J, or K cell is empty or mismatched a #VALUE error is received in O. I tested this by randomly deleting/changing values. I think my problem is in the MMULTI function, but I am unfamiliar with this function to do further troubleshooting....

UPDATE: I have updated the formula provided to:

=LET(a,rngGapAwarded,b,SORT(FILTER(a,((INDEX(a,,1)<>"")*((INDEX(a,,2)="TBD")+(INDEX(a,,2)="")))),1),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,SORT(FILTER(b,TAKE(b,,1)=e),10),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,10,11),{"@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))

Which removed errors that where being caused by non-matching data. Which has lead me to the current issue:

There is something limiting the data from this portion of the fx VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g))) to 4 rows. Does not matter how many instances get counted...if the output is greater than 4 rows a #VALUE error is received. Which can be tested with this dataset:

LCAT Employee Emp ID # Project Description Task Div Travel (Y/N) Location SY POP Start POP End Analysis Results
TEAM LEADER TBD Not Avail PROJECT#1 7/1/2023 11/1/2023 Labor Category Total Gaps Projects with Gaps
TEAM LEADER TBD Not Avail PROJECT#2 9/1/2023 12/1/2023 TEAM LEADER 5 #VALUE!
TEAM LEADER TBD Not Avail PROJECT#3 2/13/2023 3/31/2023
TEAM LEADER TBD Not Avail PROJECT#4 2/13/2023 3/31/2023
TEAM LEADER TBD Not Avail PROJECT#4 1/15/2023 3/31/2023

In this state there is an error but if J6 is changed to the same as J5, thereby limiting the row output to 4, same number of instances though, there is no error. What am I missing in the formula from @JdvJ that is setting a maximum output?

Edited out this text as not applicable.

Is this a matter of the MMULT fx? After reading up on this function, the "columns" being joined by the text join is 4. MS states for the MMULT fx:

The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.

MMULT returns the #VALUE! error when: -Any cells are empty or contain text. -The number of columns in array1 is different from the number of rows in array2.

This would make sense why a #VALUE error is received if the rows of the textjoin exceed 4. How to overcome this???

I spent the weekend testing on my actual dataset and on sample data. When I remove this section of the formula,

," Required for",TEXT(CHOOSECOLS(g,4,10,11),{"@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"})

I can get every expected line with the correct total of instances, but I obviously lose the information. With this in mind, I added back in a portion of the function:

," Required for",TEXT(CHOOSECOLS(g,4),{"@ POP:"})

Which again created errors in some rows. So what would cause this?

RandyB
  • 15
  • 6
  • 2
    Could you add some sample data with this question with expected results? Just so it's a bit easier to understand what it is you are doing. I'm also seeing some techniques that could be avoided. A simple one: `FILTER(M4#,{1,0})` == `TAKE(M4#,,1)`. – JvdV Feb 08 '23 at 12:55
  • Indeed hard to tell without data, but you probably need to use MMULT to create a counter. Counting rows on a filter will return the count of 1 if none of the criteria is met, since the filter will return either an error, or a(n empty) value, which will be counted as a row. Counting(s) needs actual range reference, while MMULT allows array multiplication. – P.b Feb 08 '23 at 13:57
  • 2
    @P.b, that is correct if one would use `COUNTA(FILTER(....))`. though as per OP `IFERROR(ROWS(FILTER(....)),0)` will return a zero if none of the criteria is met. – JvdV Feb 08 '23 at 14:04
  • @JvdV, I will work on putting some sanitized data together and upload shortly. Also, thanks for the insight on techniques to avoid; I will make changes. – RandyB Feb 08 '23 at 15:02
  • @JvdV I didn't spot that, it's actually a nice way to cope with the count of the filter result, omitting the If Empty argument of FILTER, wrapped in IFERROR/rows – P.b Feb 08 '23 at 15:49
  • @RandyB, I'm looking at the data but I'm sorry, it's not in markdown format so I can't start testing. – JvdV Feb 08 '23 at 15:56
  • 3
    @RandyB better to post sample data as data, not as a picture. You can use https://www.tablesgenerator.com/markdown_tables# to generate a table you can post on here – P.b Feb 08 '23 at 15:57
  • @JvdV, Oops! Thank you for the new tool. – RandyB Feb 08 '23 at 16:07

1 Answers1

1

I couldn't really figure out what column to sort on, nor did I know which column belongs to which named range, but tried the below to the best of my abilities trying to find out what it is you want to achieve:

enter image description here

Formula in M2:

=LET(a,A2:K25,b,SORT(FILTER(a,(INDEX(a,,2)="TBD")+(INDEX(a,,8)="")),10),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,FILTER(b,TAKE(b,,1)=e),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you! I do have an issue which I have isolated to the differences in the raw data. In my raw data, there are blank rows to separate projects; I cannot change the raw data. Formula as written, gives a #value error with blank rows in data. Need a method to discard empty rows in the "a" range. – RandyB Feb 09 '23 at 11:26
  • 1
    @RandyB It might suffice to wrap the optional boolean structure in an *anding* structure to assert column A:A is always populated. Something along these lines: `((INDEX(a,,2)="TBD")+(INDEX(a,,8)=""))*(TAKE(a,,1)<>"")` – JvdV Feb 09 '23 at 11:35
  • after looking at the actual data, there are instances where A is blank in context of other information in the data row (i.e. a labor category has not been determined, but other relevant information exists in the remaining columns of the row.) So, while this works in testing, it does not work for the actual data. I edited post to include additional "issues>" Thank you for the help! – RandyB Feb 09 '23 at 14:20
  • @RandyB, so test against another column? Swap `(TAKE(a,,1)<>"")` for `(INDEX(a,,)<>"")`. I can't further help beyond this point as I just have my sample data you provided. – JvdV Feb 09 '23 at 14:26
  • Updated with additional troubleshooting and data set. – RandyB Feb 09 '23 at 19:08
  • @JvdJ there is something limiting the data from this portion of the fx `VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))` to 4 rows. Does not matter how many get counted...but if the output is greater than 4 rows a #VALUE error is received. – RandyB Feb 10 '23 at 16:56