Few problems with dynamic arrays

[sharshra]

I´m using dynamic arrays to get the required output as mentioned below. There are few problems to be solved with this. Can the experts help please?

Source table contains name, dept & case 3. From this, I need a table which shows for each name, number of cases & dept. I´m getting partially correct output, but there are few problems to be fixed / improvements to be done.

1. For each name, I´m getting the all dept & cases together. I want to make them separate. For example, name abc should have a line each for finance & sales with count & cases also shown separately.

2a. When there is no name, count is shown calculation error. How to fix it?

2b. When there is no name, I want to show 'no name´ instead of blank. When I use IF to get 'no name', it resulting in calculation error. How to fix it? Refer the last table.

IF(UNIQUE(name)="", "no name",UNIQUE(name))

3. In the formula, only difference between nDept & caseName is the array name in FILTER. I want to create a reusable LAMBDA function within the formula & reuse. I don´t want to create thru name manager. How can it be done?

Source table:

excel problems.xlsx
	B	C	D
2	name	dept	case #
3	abc	finance	2
4	abc	finance	32
5	def	finance	32
6	efg	finance	11
7	bcd	hr	8
8		hr	60
9		hr	55
10	cde	procurement	76
11		procurement	46
12	abc	sales	93
13	def	sales	45a
14	mno	security	37
filter

Desired output:

excel problems.xlsx
	F	G	H	I
2	dept	name	count	cases
3	finance	abc	2	2, 32
4	finance	def	1	32
5	finance	efg	1	11
6	hr	bcd	1	8
7	hr	no name	2	55, 60
8	procurement	cde	1	76
9	procurement	no name	1	46
10	sales	abc	1	93
11	sales	def	1	45a
12	security	mno	1	37
filter

Dynamic array formula used:

excel problems.xlsx
	K	L	M	N
2	name	dept	cases	count
3	abc	finance, sales	2, 32, 93	3
4	def	finance, sales	32, 45a	2
5	efg	finance	11	1
6	bcd	hr	8	1
7		hr, procurement	60, 55, 46	#CALC!
8	cde	procurement	76	1
9	mno	security	37	1
filter

Cell Formulas
Range	Formula
K2:N9	K2	=LET( all, $B$3:$D$20, name, $B$3:$B$20, dept, $C$3:$C$20, case, $D$3:$D$20, uName, UNIQUE(name&""), nDept, MAP(uName, LAMBDA(x, TEXTJOIN(", ",,UNIQUE(FILTER(dept,name=x))))), caseName, MAP(uName, LAMBDA(x, TEXTJOIN(", ", ,UNIQUE(FILTER(case, name=x))))), cCount, MAP(uName, LAMBDA(x, ROWS(FILTER(case, (name=x)*(name<>""))))), heading, HSTACK({"name"}, {"dept"}, {"cases"}, {"count"}), output, VSTACK(heading, HSTACK(uName, nDept, caseName,cCount)), output)
Dynamic array formulas.

Calculation when 'no name' inserted for blank names:

excel problems.xlsx
	K	L	M	N
2	name	dept	cases	count
3	abc	finance, sales	2, 32, 93	3
4	def	finance, sales	32, 45a	2
5	efg	finance	11	1
6	bcd	hr	8	1
7	no name	#CALC!	#CALC!	#CALC!
8	cde	procurement	76	1
9	mno	security	37	1
filter

Cell Formulas
Range	Formula
K2:N9	K2	=LET( all, $B$3:$D$20, name, $B$3:$B$20, dept, $C$3:$C$20, case, $D$3:$D$20, uName, IF(UNIQUE(name)="", "no name",UNIQUE(name)), nDept, MAP(uName, LAMBDA(x, TEXTJOIN(", ",,UNIQUE(FILTER(dept,name=x))))), caseName, MAP(uName, LAMBDA(x, TEXTJOIN(", ", ,UNIQUE(FILTER(case, name=x))))), cCount, MAP(uName, LAMBDA(x, ROWS(FILTER(case, (name=x)*(name<>""))))), heading, HSTACK({"name"}, {"dept"}, {"cases"}, {"count"}), output, VSTACK(heading, HSTACK(uName, nDept, caseName,cCount)), output)
Dynamic array formulas.

 

[alansidman]

Alternatively, with a combination of Power Query and Power Pivot.

Book1
	A	B	C	D	E	F	G	H
1	name	dept	case #		dept	name	Count of case #	Cases
2	abc	finance	2		finance	abc	2	2, 32
3	abc	finance	32			def	1	32
4	def	finance	32			efg	1	11
5	efg	finance	11		hr	bcd	1	8
6	bcd	hr	8			No Name	2	60, 55
7		hr	60		procurement	cde	1	76
8		hr	55			No Name	1	46
9	cde	procurement	76		sales	abc	1	93
10		procurement	46			def	1	45a
11	abc	sales	93		security	mno	1	37
12	def	sales	45a
13	mno	security	37
Sheet1

firstly, need to populate null values with No Name in Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,null,"No Name",Replacer.ReplaceValue,{"name"})
in
    #"Replaced Value"

then close and load to the Data Model.

In Power Pivot
Create a Measure
then create your PT

 

[sharshra]

Hello again, many thanks to @Alex Blakenburg for the correction and sorry for the inconveniece, please test the following:

=LET(
a,IF(B2:C13="","no name",B2:C13),
b,UNIQUE(a),
c,BYROW(a,LAMBDA(x,CONCAT(x))),
d,BYROW(b,LAMBDA(x,CONCAT(x))),
e,MAP(d,LAMBDA(x,SUM(--(c=x)))),
f,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TEXTJOIN(", ",,SORT(FILTER(D2:D13,c=y)))))),1),
HSTACK(CHOOSECOLS(b,2,1),e,f))

Thanks, @hagia_sofia It works almost except 2 issues.
1. Header has the number instead of heading of 'count'.
2. The last row for security is missing.

excel problems.xlsx
	K	L	M	N
13	dept	name	1	case #
14	finance	abc	2	2, 32
15	finance	def	1	32
16	finance	efg	1	11
17	hr	bcd	1	8
18	hr	no name	2	55, 60
19	procurement	cde	1	76
20	procurement	no name	1	46
21	sales	abc	1	93
22	sales	def	1	45a
filter

Cell Formulas
Range	Formula
K13:N22	K13	=LET( a,IF(B2:C13="","no name",B2:C13), b,UNIQUE(a), c,BYROW(a,LAMBDA(x,CONCAT(x))), d,BYROW(b,LAMBDA(x,CONCAT(x))), e,MAP(d,LAMBDA(x,SUM(--(c=x)))), f,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TEXTJOIN(", ",,SORT(FILTER(D2:D13,c=y)))))),1), HSTACK(CHOOSECOLS(b,2,1),e,f))
Dynamic array formulas.

 

[hagia_sofia]

Thanks for the feedback! It seems like you selected the headers and not the last row - so the question is whether headers should also be part of the formula?

 

[sharshra]

I figured it out. I didn´t select the last row which had security dept Now it´s all good. Thanks a lot, @hagia_sofia. You are of great help. Really appreciate.

 

[sharshra]

Just wanted to check on the 3rd question in my first thread. I would like to create a reusable LAMBDA function within the formula.

For example, in the following lines from the current formula, only difference is the input array to BYROW function. It´s a candidate for a reusable LAMBDA function. I have created LAMBDA function thru name manager. But, I would like to create reusable LAMBDA function within the formula. Tried few but failed. I know it is simple but I´m making some mistakes. Can the experts advise please?

c,BYROW(a,LAMBDA(x,CONCAT(x))),
d,BYROW(b,LAMBDA(x,CONCAT(x))),

 

[djclements]

Just wanted to check on the 3rd question in my first thread. I would like to create a reusable LAMBDA function within the formula.

For example, in the following lines from the current formula, only difference is the input array to BYROW function. It´s a candidate for a reusable LAMBDA function. I have created LAMBDA function thru name manager. But, I would like to create reusable LAMBDA function within the formula. Tried few but failed. I know it is simple but I´m making some mistakes. Can the experts advise please?

c,BYROW(a,LAMBDA(x,CONCAT(x))),
d,BYROW(b,LAMBDA(x,CONCAT(x))),

It's not entirely clear what you mean by "within the formula". And why not thru name manager? If you define a custom LAMBDA function in name manager, it can be reused throughout the workbook. If you define it within LET, it can only be reused within that one formula.

Since you don't appear to have GROUPBY or PIVOTBY yet, I'm guessing that you don't have the new eta-lambdas either (e.g. =BYROW(array,CONCAT)). Is this what you want to create? Your own customized eta-lambdas, to be used in the [function] argument of BYROW?

To do this within LET, simply define a variable for the LAMBDA function. For example:

=LET(
    ...
    λ, LAMBDA(x,CONCAT(x)),
    c, BYROW(a,λ),
    d, BYROW(b,λ),
    ...
)

However, I still think it would be better to define it in name manager and name it CONCATλ, for example. Then you could reuse it throughout the workbook with =BYROW(array,CONCATλ).

If that's not what you meant, please provide an example of how you intend to write it (even if it doesn't work), so we can better determine what your end goal is.