Table manipulations using dynamic arrays

S

sharshra

Active Member
Joined
Mar 20, 2013
Messages
404
Office Version
  1. 365
I have to derive an output as shown in the 'desired output' table based on a 'source table' using the logic given below. I got partially correct result, but there are few issues. Can the experts help please?

Source table has the id, ph, name & dept. The output table should have the ids for the combination of name & dept in ph 1 & 2. I see that the result is incorrect when same name is in multiple dept. It just counts the name in all ph & duplicate the result for dept.

For example, trtr should have 3 rows in the output with different ids. But all ids of trtr are duplicated against depts c, l & a. Cells with incorrect output are marked in pink.

Source table:
excel problems.xlsx
BCDE
21idphnamedept
2281kjkjc
23711yuyul
24392kjkja
25541trtrc
26231nbc
2761nbc
28911trtrl
2921trtra
3072nbc
31642kjkja
Sheet8


Desired output:
excel problems.xlsx
HIJKLM
21deptnameph 1 countph1ph2 countph2
22lyuyu171
23ltrtr191
24ckjkj18
25ctrtr254
26cnb223, 617
27akjkj239, 64
28atrtr12
Sheet8


Actual output:
excel problems.xlsx
PQRSTU
21deptnameph 1 countph1ph2 countph2
22lyuyu171
23ltrtr354, 91, 2
24ckjkj18239, 64
25ctrtr354, 91, 2
26cnb223, 617
27akjkj18239, 64
28atrtr354, 91, 2
Sheet8
Cell Formulas
RangeFormula
P22:U28P22=LET( all, $B$22:$E$31, name, CHOOSECOLS(all, 3), ph, CHOOSECOLS(all, 2), dptName, SORT(UNIQUE(CHOOSECOLS(all, 4,3)),,-1), idDptName, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1))))),2),"")), idDptName1, idDptName(1), idDptName2, idDptName(2), idDptNameCount, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,ROWS(UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1)))))),2),"")), idDptNameCount1, idDptNameCount(1), idDptNameCount2, idDptNameCount(2), HSTACK(dptName,idDptNameCount1,idDptName1, idDptNameCount2,idDptName2))
Dynamic array formulas.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
MrExcelPlayground23.xlsx
BCDEFGHIJKLM
21idphnamedeptlyuyu1710
2281kjkjcltrtr1910
23711yuyulckjkj180
24392kjkjactrtr1540
25541trtrccnb223, 617
26231nbcakjkj0239, 64
2761nbcatrtr120
28911trtrl
2921trtra
3072nbc
31642kjkja
Sheet22
Cell Formulas
RangeFormula
H21:M27H21=LET(a,B22:E31,b,TAKE(a,,-2),c,UNIQUE(b),d,SORT(CHOOSECOLS(c,2,1),1,-1),e,CHOOSECOLS(b,2)&"-"&CHOOSECOLS(b,1),f,CHOOSECOLS(d,1)&"-"&CHOOSECOLS(d,2),g,(--(TOROW(e)=f)*(TOROW(CHOOSECOLS(a,2)=1))*TRANSPOSE(CHOOSECOLS(a,1))),gg,IF(g=0,"",g),ggg,BYROW(gg,LAMBDA(x,TEXTJOIN(", ",TRUE,x))),h,MMULT(--(TOROW(e)=f)*(TOROW(CHOOSECOLS(a,2)=1)),SEQUENCE(ROWS(a),1,1,0)),i,(--(TOROW(e)=f)*(TOROW(CHOOSECOLS(a,2)=2))*TRANSPOSE(CHOOSECOLS(a,1))),ii,IF(i=0,"",i),iii,BYROW(ii,LAMBDA(x,TEXTJOIN(", ",TRUE,x))),j,MMULT(--(TOROW(e)=f)*(TOROW(CHOOSECOLS(a,2)=2)),SEQUENCE(ROWS(a),1,1,0)),k,HSTACK(d,h,ggg,j,iii),k)
Dynamic array formulas.
 
Upvote 1
Another option
Fluff.xlsm
BCDEFGHIJKLM
21idphnamedeptdeptnameph 1 countph1ph2 countph2
2281kjkjclyuyu171
23711yuyulltrtr191
24392kjkjactrtr154
25541trtrccnb223, 617
26231nbcckjkj18
2761nbcatrtr12
28911trtrlakjkj239, 64
2921trtra
3072nbc
31642kjkja
Main
Cell Formulas
RangeFormula
H22:M28H22=DROP(PIVOTBY(CHOOSECOLS(D22:E31,2,1),C22:C31,B22:B31,HSTACK(COUNT,ARRAYTOTEXT),0,0,{-1,-2},0),2)
Dynamic array formulas.
 
Upvote 1
Thanks, @JamesCanale. It works perfectly. (y) Thanks to you too, @Fluff. Unfortunately, I don´t have the PIVOTBY.

However, I would like to know what is wrong in the formula that I used. Can the experts help to fix the formula I used please?
 
Upvote 0
I don't have pivotby either - I was amazed at the conciseness of Fluffs.

I can barely understand my solution - taking apart another one is really hard.
 
Upvote 0
sharshra said:
Thanks, @JamesCanale. It works perfectly. (y) Thanks to you too, @Fluff. Unfortunately, I don´t have the PIVOTBY.

However, I would like to know what is wrong in the formula that I used. Can the experts help to fix the formula I used please?
Click to expand...
Hello experts, any suggestions on what is wrong with the formula & how to correct it?

excel problems.xlsx
PQRSTU
21deptnameph 1 countph1ph2 countph2
22lyuyu171
23ltrtr354, 91, 2
24ckjkj18239, 64
25ctrtr354, 91, 2
26cnb223, 617
27akjkj18239, 64
28atrtr354, 91, 2
Sheet8
Cell Formulas
RangeFormula
P22:U28P22=LET( all, $B$22:$E$31, name, CHOOSECOLS(all, 3), ph, CHOOSECOLS(all, 2), dptName, SORT(UNIQUE(CHOOSECOLS(all, 4,3)),,-1), idDptName, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1))))),2),"")), idDptName1, idDptName(1), idDptName2, idDptName(2), idDptNameCount, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,ROWS(UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1)))))),2),"")), idDptNameCount1, idDptNameCount(1), idDptNameCount2, idDptNameCount(2), HSTACK(dptName,idDptNameCount1,idDptName1, idDptNameCount2,idDptName2))
Dynamic array formulas.
 
Upvote 0
You are only filtering on name & ph, but you need to filter on name, dept & ph
 
Upvote 0
Thanks, @Fluff. You are correct. I´m struggling to add dept in the filter. It is giving incorrect output :( I must be making some mistake, but couldn´t figure out.
 
Upvote 0
You can do it like
Excel Formula: 
=LET(
all, $B$22:$E$31, name, CHOOSECOLS(all, 3), ph, CHOOSECOLS(all, 2),dept,CHOOSECOLS(all,4),
dptName, SORT(UNIQUE(CHOOSECOLS(all, 4,3)),,-1),
idDptName, LAMBDA(phs, IFERROR(MAP(INDEX(dptName,,2),INDEX(dptName,,1), LAMBDA(x,y, TEXTJOIN(", ", ,UNIQUE(FILTER(INDEX(all,,1), (dept=y)*(name=x)*(ph=phs)))))),"")),
idDptName1, idDptName(1), idDptName2, idDptName(2),
idDptNameCount, LAMBDA(phs, IFERROR(MAP(INDEX(dptName,,2),INDEX(dptName,,1), LAMBDA(x,y, TEXTJOIN(", ", ,ROWS(UNIQUE(FILTER(INDEX(all,,1),(dept=y)*(name=x)*(ph=phs))))))),"")),
idDptNameCount1, idDptNameCount(1), idDptNameCount2, idDptNameCount(2),
HSTACK(dptName,idDptNameCount1,idDptName1, idDptNameCount2,idDptName2))
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Convert to dynamic array
Replies
7
Views
183
sharshra
S
S
  • Solved
Repeating rows
Replies
2
Views
213
sharshra
S
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
325
djclements
djclements
S
  • Solved
Data manipulation with dynamic arrays
Replies
5
Views
77
Fluff
Fluff
H
  • Question Question
Help filtering a table using LET, UNIQUE functions
Replies
6
Views
100
Dave Patton
D

Forum statistics

Threads
1,224,812
Messages
6,181,088
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top