A Little Buried in My Own Formula

[ArmstrongArcade]

Hey Chandoo'ers,

I am a little lost in my own creation, everything is working in the formula but I cannot figure a way to return unique PayDescription and associated DivisionDepartmentNo. If I run UNIQUE(DivisionDepartmentNo,$CG$12:$CH$3000), or UNIQUE(PayDescription,$CJ$12:$CJ$3000), the formula returns with #N/A's.
Any help or comment, or suggestion would be appriciated. Thank you.

The current return:

GLs	Pay Descriptions
10-102	Earning-Rate 1 - Reg
10-102	Earning-Rate 1 - Reg
10-102	Earning-Rate 1 - Reg
10-102	Earning-Rate 1 - Reg
10-102	Earning-PTO - Reg

The return I am aiming for:

GLs	Pay Descriptions
10-102	Earning-Rate 1 - Reg
10-102	Earning-PTO - Reg

Note: I understand and use LAMBDA, but I know I don't fully comprehend nor utilize it to its full potential.

=LET(
 ReconDivDept,UNIQUE(SORT(FILTER(DX2:DX250,DX2:DX250<>""))),
 PayrollTaxes,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"PayrollTaxes")),
 WC_LPI,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"WC&LPI")),
 Benefits,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"Benefits")),
 HR_FT,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"HRServices&ComplianceFT")),
 HR_PT,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"HRServices&CompliancePT")),
 CoEmpRisk,MAKEARRAY(ROWS(ReconDivDept),1,LAMBDA(r,c,"Co-Emp.Risk")),
Here>DivisionDepartmentNo,$CG$12:$CH$3000,
Here>PayDescription,$CJ$12:$CJ$3000,
  CleanedDivDept,FILTER(BYROW(DivisionDepartmentNo,LAMBDA(row,IF(COUNTA(row)=0,"",TEXTJOIN("-",TRUE,SUBSTITUTE(row,"'",""))))),BYROW(DivisionDepartmentNo,LAMBDA(row,COUNTA(row)>0))),
  CleanedPayDesc,FILTER(PayDescription,PayDescription<>""),
   VSTACK(
    HSTACK(CleanedDivDept,CleanedPayDesc),
    HSTACK(ReconDivDept,PayrollTaxes),
    HSTACK(ReconDivDept,WC_LPI),
    HSTACK(ReconDivDept,Benefits),
    HSTACK(ReconDivDept,HR_FT),
    HSTACK(ReconDivDept,HR_PT),
    HSTACK(ReconDivDept,CoEmpRisk)))

 

[AlanSidman]

Another approach with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Location] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Bill Grp"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

 

[ArmstrongArcade]

Another approach with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Location] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Bill Grp"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Yea, absolutely, and I originally was going to go that route. However, if I ever leave this position, I've been working to redo my workbooks using just formulas instead of VBA and PQs.

 

[ArmstrongArcade]

Question answered.
Grok to the rescue, I couldn't get it to work the last few days, probably my wording with the system. But, I believe this got it, the numbers total out at least.

=LET(
    ReconDivDept, UNIQUE(SORT(FILTER(DX2:DX250, DX2:DX250<>""))),
    PayrollTaxes, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"PayrollTaxes")),
    WC_LPI, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"WC&LPI")),
    Benefits, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"Benefits")),
    HR_FT, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"HRServices&ComplianceFT")),
    HR_PT, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"HRServices&CompliancePT")),
    CoEmpRisk, MAKEARRAY(ROWS(ReconDivDept), 1, LAMBDA(r,c,"Co-Emp.Risk")),
    DivisionDepartmentNo, $CG$12:$CH$3000,
    PayDescription, $CJ$12:$CJ$3000,
    CleanedDivDept, FILTER(BYROW(DivisionDepartmentNo, LAMBDA(row, IF(COUNTA(row)=0,"",TEXTJOIN("-",TRUE,SUBSTITUTE(row,"'",""))))), BYROW(DivisionDepartmentNo, LAMBDA(row,COUNTA(row)>0))),
    CleanedPayDesc, FILTER(PayDescription, PayDescription<>""),
    UniquePairs, UNIQUE(HSTACK(CleanedDivDept, CleanedPayDesc)),
    VSTACK(
        UniquePairs,
        HSTACK(ReconDivDept, PayrollTaxes),
        HSTACK(ReconDivDept, WC_LPI),
        HSTACK(ReconDivDept, Benefits),
        HSTACK(ReconDivDept, HR_FT),
        HSTACK(ReconDivDept, HR_PT),
        HSTACK(ReconDivDept, CoEmpRisk)
    )
)

 

[AliGW]

Doesn't this do what you want?

=LET(u,UNIQUE(CG12:CJ1428),
GLs,SUBSTITUTE(INDEX(u,,1)&"-"&INDEX(u,,2),"'",""),
h,HSTACK(GLs,INDEX(u,,4)),
FILTER(h,GLs<>"-"))

 

[djc]

Instead of using MAKEARRAY to repeat each of the individual descriptions, then using VSTACK/HSTACK to join them all together, you can use IFNA to broadcast the ReconDivDept variable (vertical vector) across a horizontal vector of descriptions (and vice-versa), then use TOCOL to send the resulting array(s) to a single column. It's also possible to include the third "SUMIFS" column in the same formula, using MAP with XMATCH and CHOOSE to select the applicable sum_range for each GL code in the array.

=LET(
   UniqueData, UNIQUE(CHOOSECOLS(FILTER(CG12:CJ3000,CG12:CG3000<>""),1,2,4)),
   ReconDivDept, UNIQUE(SORT(FILTER(DX2:DX250,DX2:DX250<>""))),
   ReconDivDesc, HSTACK("PayrollTaxes","WC&LPI","Benefits","HRServices&ComplianceFT","HRServices&CompliancePT","Co-Emp.Risk"),
   dept, VSTACK(BYROW(SUBSTITUTE(TAKE(UniqueData,,2),"'",),LAMBDA(x,TEXTJOIN("-",,x))),TOCOL(IFNA(ReconDivDept,ReconDivDesc),,1)),
   desc, VSTACK(TAKE(UniqueData,,-1),TOCOL(IFNA(ReconDivDesc,ReconDivDept),,1)),
   vals, MAP(dept,desc,XMATCH(desc,ReconDivDesc),LAMBDA(gl,dsc,id,
      IF(gl="","",IF(ISNUMBER(id),SUMIFS(CHOOSE(id,DL:DL,DM:DM,DN:DN,DP:DP,DR:DR,DS:DS),DX:DX,gl),
         SUMIFS(CO:CO,CJ:CJ,dsc,CG:CG,"'"&TEXTBEFORE(gl,"-")&"'",CH:CH,"'"&TEXTAFTER(gl,"-")&"'"))))),
   HSTACK(dept,desc,vals)
)

Cheers!