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.

 

[hagia_sofia]

Hello, this seems like a perfect job for the new GROUPBY function, e.g. something like:

=LET(
a,HSTACK(C2:C13,B2:B13),
b,IF(a="","no name",a),
c,GROUPBY(b,D2:D13,COUNTA,,0),
d,GROUPBY(b,D2:D13,ARRAYTOTEXT,,0),
HSTACK(c,CHOOSECOLS(d,3)))

 

[hagia_sofia]

It seems that it is possible to use multiple functions at once within GROUPBY which makes the formula even shorter:

=LET(
a,HSTACK(C2:C13,B2:B13),
b,IF(a="","no name",a),
DROP(GROUPBY(b,D2:D13,HSTACK(COUNTA,ARRAYTOTEXT),0,0),1))

 

[sharshra]

Thanks, @hagia_sofia. Is GROUPBY available in O365 version? I´m getting an error. It is not recognizing GROUPBY

excel problems.xlsx
	K
13	#NAME?
filter

Cell Formulas
Range	Formula
K13	K13	=LET( a,HSTACK(C2:C13,B2:B13), b,IF(a="","no name",a), c,GROUPBY(b,D2:D13,COUNTA,,0), d,GROUPBY(b,D2:D13,ARRAYTOTEXT,,0), HSTACK(c,CHOOSECOLS(d,3)))

 

[hagia_sofia]

It might not be (not sure about the calendar but GROUPBY and PIVOTBY recently dropped into my Excel) - try to update your 365.

 

[sharshra]

Oops....the end users will have to use the spreadsheet with their current O365 only. Can the desired output be obtained with current version of O365 which doesn´t support GROUPBY?

 

[hagia_sofia]

After a check it seems that GROUPBY should now be available for all users.

 

[Alex Blakenburg]

After a check it seems that GROUPBY should now be available for all users.

That post is quite recent and says that it is now available to all users on the "Current Channel". The Monthly Enterprise Channel should get it soon but those on the "Semi-Annual Enterprise Channel" are likely to have a bit of a wait.

Current Channel

All Channels

 

[sharshra]

OK. Good to know that it will be available soon Unfortunately, I & the end users of the spreadsheet will need this now. Any suggestions on completing the formula will be of great help.

 

[hagia_sofia]

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))