Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Mar 11, 2026
Solved

Data Formatting

Dear Experts,

                    I have a Data like below :-

Each row can have 1 or more RNTIs( seperated by ",") and their DCI( UL_01 or UL_00), 

I want to put them in a format like in Col - I~P gave examples for the 1st two entries.

Attached worksheet.

Thanks & Regards

Anupam Shrivastava

BI & Data Analysis
Charting
excel
Formulas and Functions
office 365
power bi
  • OliverScheurich's avatar
    OliverScheurich
    Mar 11, 2026

    An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

    Thanks a lot PeterBartholomew1​ for providing the sample data in an Excel file and for the 365 solution.

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 11, 2026

    An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

    Thanks a lot PeterBartholomew1​ for providing the sample data in an Excel file and for the 365 solution.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 11, 2026

    To be honest, I am not sure whether this 365 solution will be of any value to you, it uses specially written versions of MAP to overcome the array of array problem in Excel 365.  The problem-specific formulas are

    Worksheet formula
= MAPλ(Table1[sfn], Table1[RNTI], Table1[DCI], UNPIVOT.CSVλ)

where

UNPIVOT.CSVλ
= LAMBDA(sfn, RNTI, dci,
    HSTACK(
        EXPAND(sfn, COUNTA(TEXTSPLIT(RNTI, , ",")), , sfn),
        TEXTSPLIT(RNTI, , ","),
        TEXTSPLIT(dci, , ",")
    )
);

    The catch is the function MAPλ that I published on GitHub is not straightforward, though it may be easy enough to use.

     

    • anupambit1797's avatar
      anupambit1797
      Steel Contributor
      Mar 11, 2026

      Thank you PeterBartholomew1​ , sorry to not mentioned earlier that the criteria to choose should be a combination of hfnTickCount-sfn-slot , rather than sfn only as after a cycle sfn and slot will be repeated but the combination of these 3 will be unique

      Also, could you please help share the link to that Github you mentioned , any Tutorial to get through these creation of customized "lambdas".

      It's really interesting for me to know that excel does't know that "UNPIVOT.CSVλ", still how come it executed that.. Good to know that too..Thanks!

      Thanks in Advance,

      Br,

      Anupam