Convert to dynamic array

S

sharshra

Active Member
Joined
Mar 20, 2013
Messages
404
Office Version
  1. 365
I´m using a formula to achieve the desired result & want to convert it to dynamic array. I tried using MAP function with LAMBDA, but it is failing. Can the experts guide please?

Rules -
Compare source table with another table. If a dept is marked 'x' in the source table & if it exists in the 2nd table, mark 'x' in the output. If not, mark, mark 'no'. If source table is not marked 'x', enter 'NA' in the output.

I´m getting the desired output when I copy & paste the formula. But, when I tried to convert it to dynamic array, it is throwing error.

Source table:
excel problems.xlsx
BCDE
25nameemailsaleshr
26ffaffa@123.comx
27jkkjjkkj@123.comx
28sasasasa@123.comxx
29gfgfgfgf@123.comx
30trtr1trtr1@123.comx
31oioioioi@123.comx
32nmnnmn@123.comx
33cxcxbcxcxb@123.com
34nhnhnhnh@123.com
35joojjooj@123.comxx
merge tables


Compare table:
excel problems.xlsx
GH
25deptemail
26salesffa@123.com
27salessasa@123.com
28salestrtr1@123.com
29hrjkkj@123.com
30hrsasa@123.com
merge tables


Desired output:
excel problems.xlsx
JKLMNO
25nameemailsaleshrcomp-salescomp-hr
26ffaffa@123.comxyesNA
27jkkjjkkj@123.comxNAyes
28sasasasa@123.comxxyesyes
29gfgfgfgf@123.comxnoNA
30trtr1trtr1@123.comxyesNA
31oioioioi@123.comxNAno
32nmnnmn@123.comxnoNA
33cxcxbcxcxb@123.comNANA
34nhnhnhnh@123.comNANA
35joojjooj@123.comxxnono
merge tables
Cell Formulas
RangeFormula
N26:N35N26=LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, IF(D26="x",IFNA(IF(LEN(XLOOKUP(C26,CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"), a)
O26:O35O26=LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, IF(E26="x",IFNA(IF(LEN(XLOOKUP(C26,CHOOSECOLS(cEmailHr,2), CHOOSECOLS(cEmailHr,2)))>0,"yes"),"no"),"NA"), a)


Dynamic array:
excel problems.xlsx
R
26#CALC!
merge tables
Cell Formulas
RangeFormula
R26R26=LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, MAP(tSource, LAMBDA(src, IF(CHOOSECOLS(tSource,2)="x",IFNA(IF(LEN(XLOOKUP(CHOOSECOLS(sEmail,2),CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"))), a)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
No need for lambda's ;p

Excel Formula: 
=IF(IF(D26:E35="x",1,NA())*ISNUMBER(MATCH(C26:C35,H26:H30,0)),"yes","no")
 
Upvote 0
Just in cell J26:
Excel Formula: 
=LET(a,B26:E35,b,H26:H30,STACKH(a,IF(CHOOSECOLS(a,3,4)="x",IF(IFERROR(XMATCH(CHOOSECOLS(a,2),b,0),0)>0,"yes","no"),"NA")))
 
Last edited:
Upvote 0
Thanks, @JEC & @DanteAmor. Both solutions work for me.

Adding a bit more complexity with additional columns. It´s working when I copy & paste the formula, but not getting the correct result when dynamic array is used. I´m sure I would have made a minor mistake. Can the experts in this forum help please?

Source table:
excel problems.xlsx
BCDEFG
42nameemailsales-notrhsales-southhr-northhr-south
43ffaffa@123.comx
44jkkjjkkj@123.comx
45sasasasa@123.comxx
46gfgfgfgf@123.comx
47trtr1trtr1@123.comx
48oioioioi@123.comx
49nmnnmn@123.comx
50cxcxbcxcxb@123.com
51nhnhnhnh@123.com
52joojjooj@123.comxx
merge tables


Table 2:
excel problems.xlsx
IJ
42deptemail
43salesffa@123.com
44salessasa@123.com
45salestrtr1@123.com
46hrjkkj@123.com
47hrsasa@123.com
merge tables


Desired output: Need to get similar output using dynamic arrays.
excel problems.xlsx
LMNO
42nameemailcomp-salescomp-hr
43ffaffa@123.comyesNA
44jkkjjkkj@123.comNAyes
45sasasasa@123.comyesyes
46gfgfgfgf@123.comnoNA
47trtr1trtr1@123.comyesNA
48oioioioi@123.comNAno
49nmnnmn@123.comnoNA
50cxcxbcxcxb@123.comNANA
51nhnhnhnh@123.comNANA
52joojjooj@123.comnono
merge tables
Cell Formulas
RangeFormula
N43:N52N43=LET( sEmail, tSource15[email], cEmail, tComp16[email], cDept, tComp16[dept], cEmailSales, FILTER(tComp16, cDept="sales"), cEmailHr, FILTER(tComp16, cDept="hr"), a, IF(OR(D43="x",E43="x"),IFNA(IF(LEN(XLOOKUP(C43,CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"), a)
O43:O52O43=LET( sEmail, tSource15[email], cEmail, tComp16[email], cDept, tComp16[dept], cEmailSales, FILTER(tComp16, cDept="sales"), cEmailHr, FILTER(tComp16, cDept="hr"), a, IF(OR(F43="x",G43="x"),IFNA(IF(LEN(XLOOKUP(C43,CHOOSECOLS(cEmailHr,2), CHOOSECOLS(cEmailHr,2)))>0,"yes"),"no"),"NA"), a)
 
Upvote 0
sharshra said:
with additional columns
Click to expand...

Just in L43:

=LET(a,B43:G52,b,J43:J47,STACKH(a,IF(CHOOSECOLS(a,3,4,5,6)="x",IF(IFERROR(XMATCH(CHOOSECOLS(a,2),b,0),0)>0,"yes","no"),"NA")))

The formula includes the data from the first table and the results.
You only need to put the headers in row L42 to U42

😇
 
Upvote 0
Thanks, @DanteAmor. This gives me 4 columns in the output corresponding to 4 columns in the source table. I basically need a combined column each for sales & hr.

excel problems.xlsx
LMNOPQ
55nameemail
56ffaffa@123.comyesNANANA
57jkkjjkkj@123.comNANAyesNA
58sasasasa@123.comyesNANAyes
59gfgfgfgf@123.comnoNANANA
60trtr1trtr1@123.comNAyesNANA
61oioioioi@123.comNANAnoNA
62nmnnmn@123.comnoNANANA
63cxcxbcxcxb@123.comNANANANA
64nhnhnhnh@123.comNANANANA
65joojjooj@123.comnoNAnoNA
merge tables
Cell Formulas
RangeFormula
L56:Q65L56=LET( a,B43:G52,b,J43:J47,HSTACK(CHOOSECOLS(a,1,2),IF(CHOOSECOLS(a,3,4,5,6)="x",IF(IFERROR(XMATCH(CHOOSECOLS(a,2),b,0),0)>0,"yes","no"),"NA")))
Dynamic array formulas.

a,B43:G52,b,J43:J47,HSTACK(a,IF(CHOOSECOLS(a,3,4,5,6)="x",IF(IFERROR(XMATCH(CHOOSECOLS(a,2),b,0),0)>0,"yes","no"),"NA")))[/XD][/XR][/RANGE]

Desired output:
excel problems.xlsx
LMNO
42nameemailcomp-salescomp-hr
43ffaffa@123.comyesNA
44jkkjjkkj@123.comNAyes
45sasasasa@123.comyesyes
46gfgfgfgf@123.comnoNA
47trtr1trtr1@123.comyesNA
48oioioioi@123.comNAno
49nmnnmn@123.comnoNA
50cxcxbcxcxb@123.comNANA
51nhnhnhnh@123.comNANA
52joojjooj@123.comnono
merge tables
Cell Formulas
RangeFormula
N43:N52N43=LET( sEmail, tSource15[email], cEmail, tComp16[email], cDept, tComp16[dept], cEmailSales, FILTER(tComp16, cDept="sales"), cEmailHr, FILTER(tComp16, cDept="hr"), a, IF(OR(D43="x",E43="x"),IFNA(IF(LEN(XLOOKUP(C43,CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"), a)
O43:O52O43=LET( sEmail, tSource15[email], cEmail, tComp16[email], cDept, tComp16[dept], cEmailSales, FILTER(tComp16, cDept="sales"), cEmailHr, FILTER(tComp16, cDept="hr"), a, IF(OR(F43="x",G43="x"),IFNA(IF(LEN(XLOOKUP(C43,CHOOSECOLS(cEmailHr,2), CHOOSECOLS(cEmailHr,2)))>0,"yes"),"no"),"NA"), a)
 
Upvote 0
In L43:

Excel Formula: 
=LET(a,B43:G52,b,J43:J47,c,STACKH(CHOOSECOLS(a,3)&CHOOSECOLS(a,4),CHOOSECOLS(a,5)&CHOOSECOLS(a,6)),STACKH(CHOOSECOLS(a,1,2),IF(c<>"",IF(IFERROR(XMATCH(CHOOSECOLS(a,2),b,0),0)>0,"yes","no"),"NA")))
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Repeating rows
Replies
2
Views
213
sharshra
S
S
  • Solved
Problem with XLOOKUP on array
Replies
3
Views
242
Fluff
Fluff
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
144
GMC The Macro Man
GMC The Macro Man
S
  • Solved
Use dynamic array
Replies
4
Views
171
dreid1011
dreid1011
S
  • Solved
Table manipulations using dynamic arrays
2
Replies
10
Views
149
Fluff
Fluff

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