Forum Discussion

ColinJHarrison's avatar
ColinJHarrison
Brass Contributor
Oct 04, 2022
Solved

Getting multiple values against unique names...

Hi,

 

I have a data set with names in one column (A) and values in another (B). Each name:value pair is unique, but both A and B have duplicates.

 

I need to extract a list of unique names (that's easy enough UNIQUE(A:A)), but then next to each name, somehow list (horizontally - i.e. catenated into a single string in one cell, or else spread horizontally across columns) all the values that occur against that name.

 

Any suggestions as to how I might do this?

 

Thanks

excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 04, 2022

    ColinJHarrison 

    It is also possible to return a list of arrays, each containing the values corresponding to one of the names.  It is, unfortunately, made insanely difficult by an incredibly bad decision made by Microsoft when specifying the new array functionality.  Namely, nested arrays were to be treated as errors rather than a fundamental building block of building solutions.

     

    In the present case one should be able to use MAP to run through the distinct names and have Excel collect the filtered value arrays into a 2D array.  Such logic did not prevail and, instead, unpleasant workarounds are needed.

    = LET(
     distinctName, UNIQUE(Table1[Name]),
     array, REDUCE("", distinctName,
         LAMBDA(acc,n,
            VSTACK(acc, TRANSPOSE(FILTER(Table1[Value], Table1[Name]=n)))
         )
     ),
     IFERROR(DROP(array,1),"")
  )

    It is possible to pretty the formula up a bit, but only by plunging into even more advanced techniques to solve a problem that should have been well within scope for the native functions.

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 04, 2022

      The steps to tidy the formula up are insanely complicated for such a simple task (or maybe it's just me getting senile!).  To use any built-in function within a helper function, the built-in function needs to be wrapped as a Lambda function.

       

      That is OK, 

      Filterλ 
= LAMBDA(arr, crit,
     LAMBDA(acc,n,
         VSTACK(acc, TRANSPOSE(FILTER(arr, crit=n)))
     )
  );

      but the presence of VSTACK suggests that all is not well.  Next, the REDUCE element of the formula needs to be wrapped in a further Lambda function to hide the complexity.  The intent of the REDUCE/VSTACK combination is to perform a MAP operation without triggering the 'nested arrays' error.  The worksheet formula is now

      Mapλ
= LAMBDA(name, Fnλ,
    REDUCE(hdr, name, Fnλ)
);

      The other trick is that the partially satisfied Filterλ Lambda function (itself a Lambda function is passed as a parameter to Mapλ, giving a final form for the worksheet function, 

      = LET(
     array, Mapλ(UNIQUE(Table1[Name]), Filterλ(Table1[Value], Table1[Name])),
     IFERROR(DROP(array,1),"")
  )

      which is far more complicated than it should be.

       

       

      • ColinJHarrison's avatar
        ColinJHarrison
        Brass Contributor
        Oct 04, 2022
        Thanks Peter. This answer is way beyond my level of competence. But I hope your insights will be useful to anyone else following this thread. Cheers!

Resources