excelexcel-formulaexcel-365

Map Function - Nested Arrays are not supported


enter image description here

Analysis worksheet looks like this right now. Sorry about it not being in markup.

I have a list of months on column A, a count of duplicate aging retro numbers in column B, the duplicate aging case numbers in column C. Column C works and pulls the information from my data table - and I have used column C for my actual report.

Columns E through N (and beyond) are all case numbers that received payments in a particular month for a particular agreement, but the job is to find which ones have gotten duplicates. And we get that through comparing which have gotten retro payments in the same month. Also they seem to be mis-aligned.

The reason for the question is that I'd really like to be able to look in column C, and conditionally format my data table held in another worksheet so that the case numbers in that worksheet can be highlighted; those case numbers are duplicates, they need to be identified, and appropriate corrective action needs to be taken for them. As it stands right now, I'm doing a manual process of filtering the data table for these numbers, and I'd rather not do that.

I wanted to be able to combine all of these numbers in to a single array, so then I could put conditional formatting for my data table to say 'Is the number in my data table in the list of duplicates? If so, format it.

enter image description here

See the source data. You can see for instance, the case number 13138858 appears twice, and has a non zero number in 'retro' and a non zero number in the same month (august). This means it's a duplicate occurrence and I need to track this. However, the number 9556 appears twice, but doesn't meet that criteria; it just means two payments happened to this account during two different periods, none of which were retro payments. The retro units can be 1, -1, or 0; the 'month' units can be 1, -1, or 0, but the only time I care is when retro units and the month units for that particular case number are non-zero during the same month.

Case Number Retro Start Date Retro End Date Retro Units December November October September August July June May April March
1857460 1-May-24 31-May-24 1 1
1858575 1-Apr-24 30-Apr-24 1 1
1858575 1-Apr-24 30-Jun-24 1 1 1 1
1892043 1-Mar-24 31-Mar-24 1 1
1892043 1-Mar-24 30-Jun-24 1 1 1 1 1
1893337 1-Mar-24 30-Jun-24 1 1 1 1 1
1893337 1-Mar-24 31-Mar-24 1 1
1896176 1-Apr-24 30-Apr-24 1 1
1896176 1-Apr-24 30-Jun-24 1 1 1 1
13102088 1-Jun-24 30-Jun-24 1 1
13102088 1-Jun-24 30-Jun-24 1 1
13138858 1-Jun-24 30-Jun-24 1 1
13138858 1-Jun-24 30-Jun-24 1 1
9556 1-Jan-22 31-May-22 0
9556 1-Jun-21 31-Oct-21 0
12678 1-Mar-24 30-Jun-24 0 0 0 0 0
12678 1-May-24 30-Jun-24 0 0 0
12678 1-Jun-24 30-Jun-24 0 0
13465 1-Mar-24 30-Jun-24 0 0 0 0 0

Solution

  • As commented above you could try using the following, using one or the other methods:

    • Using MAP()

    enter image description here


    =LET(
     _Head, July24_Details_1[#Headers],
     _Output, MAP(A8#, LAMBDA(a, LET(b, INDEX(July24_Details_1,,XMATCH(a, _Head)),
                        c, XMATCH(July24_Details_1[[#Headers],[Case Number]],_Head),
              TEXTJOIN(", ",1,IFNA(INDEX(FILTER(July24_Details_1, July24_Details_1[Retro Units]*b),,c),""))))),
     _Delim, SEQUENCE(,MAX(LEN(_Output)-LEN(SUBSTITUTE(_Output,",",))+1)),
     IFERROR(TEXTSPLIT(TEXTAFTER(","&_Output,",",_Delim),",",,1),""))
    

    • Or, Using REDUCE()

    =LET(
         _Head, July24_Details_1[#Headers],
         DROP(IFNA(REDUCE("",A8#,LAMBDA(r,c,VSTACK(r,
         IFNA(TOROW(INDEX(FILTER(July24_Details_1,July24_Details_1[Retro Units]*INDEX(July24_Details_1,,XMATCH(c,_Head))),,
         XMATCH(July24_Details_1[[#Headers],[Case Number]],_Head))),"")))),""),1))
    

    Edit: Updated Solution Per OP's new edit after posting a solution which was working per the given context in the op:

    enter image description here


    =LET(
     _Output, BYROW(A23#,LAMBDA(a, LET(
              b, TOROW(XMATCH(a,Table4[#Headers]),2),
              c, CHOOSECOLS(Table4,b),
              d, c*Table4[Retro Units],
              TEXTJOIN(", ",1,IFERROR(UNIQUE(FILTER(Table4[Case Number],d)),""))))),
     _Delim, SEQUENCE(,MAX(LEN(_Output)-LEN(SUBSTITUTE(_Output,",",))+1)),
     IFERROR(TEXTSPLIT(TEXTAFTER(","&_Output,",",_Delim),","),""))
    

    Using MAP():

    =LET(
     _Output, MAP(A23#,LAMBDA(a, LET(
              b, TOROW(XMATCH(a,Table4[#Headers]),2),
              c, CHOOSECOLS(Table4,b),
              d, c*Table4[Retro Units],
              TEXTJOIN(", ",1,IFERROR(UNIQUE(FILTER(Table4[Case Number],d)),""))))),
     _Delim, SEQUENCE(,MAX(LEN(_Output)-LEN(SUBSTITUTE(_Output,",",))+1)),
     IFERROR(TEXTSPLIT(TEXTAFTER(","&_Output,",",_Delim),","),""))