generate a list of instances

Copper Contributor

Hello. Please see this example of what I'm trying to do. I don't want to have to come manually filter my table every time. I want a formula that will generate a list of all case numbers that have a severity C type Dog. My actual table is enormous, and this list will actually be on a different sheet. Thanks so much!Screenshot 2024-06-17 094852.png

4 Replies


Hi, there is also a function for filtering. The formula is

=FILTER(myTable[Case No],(myTable[Severit]="c")*(myTable[Type]="dog"))


Thank you. Would this not just generate a filtered table?
Maybe I also need to include an "arraytotext" formula?



=LET(a,FILTER(myTable[Case No],(myTable[Severit]="c")*(myTable[Type]="dog")),b,BYROW(UNIQUE(a),LAMBDA(in,SUMPRODUCT(--(in=a)))),c,UNIQUE(a),TEXTJOIN(", ",TRUE,"Case "&IF(b=1,c,c&" ("&b&" times)")))

= Case 2 (2 times), Case 5