Forum Discussion

Dakkshinamurthi Pan Subramanian's avatar
Dakkshinamurthi Pan Subramanian
Copper Contributor
Aug 31, 2017
Solved

Array formula not working in my computer

Hallo People,   I want to extract values of a row which has duplicates into seperate columns. I used the below formula {=https://exceljet.net/excel-functions/excel-iferror-function(https://exce...
excel
Excel Online
Formulas and Functions
  • Dakkshinamurthi Pan Subramanian's avatar
    Dakkshinamurthi Pan Subramanian
    Sep 01, 2017

    Hi, 

     

    It is working !! The syntax was wrong.

    =IFERROR(INDEX(names;SMALL(IF(groups=$E5; ROW(names)-MIN(ROW(names))+1);COLUMNS($E$5:E5)));"")

     

    This worked. The snytax on this excel was seperated by semi colons and not commas. 

    Thanks and best regards,

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 04, 2022

    PeterBartholomew1 

    If literally

    =LET(
    groups, UNIQUE(Groups[Group]),
    names, MAP(
        groups,
        LAMBDA(grp,
            TEXTJOIN(
                ", ",
                ,
                FILTER(Groups[Name], Groups[Group] = grp)
            )
        )
    ),
    VSTACK(Groups[#Headers], HSTACK(groups, names))
)
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Apr 04, 2022

      SergeiBaklan 

      Looks elegant!  I tended to avoid stacking data arrays with their headers on the grounds that, though they may be part of the same table, a header is not part of the array.  I think I may reconsider on the grounds that the latest batch of functions make referencing the data structure reasonably easy and self-contained.

      = LET(
    hdr,  TAKE(stackedTbl,1),
    data, DROP(stackedTbl,1),
    XLOOKUP(h, hdr, data)
  )

Resources