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 & RegardsAnupam Shrivastava

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.

Data Formatting | Microsoft Community Hub

5 Replies

IlirU

Iron Contributor

Mar 12, 2026

anupambit1797,

Solution by formula (if you want it).

Apply below formula in cell J1:

=LET(
 d, A1:H24,
 t, DROP(d, 1),
tk, TAKE(TAKE(t,, -2),, 1),
    VSTACK(TAKE(d, 1),
    HSTACK(--WRAPROWS(TOCOL(TEXTSPLIT(ARRAYTOTEXT(REPT(BYROW(DROP(t,, -2) & ", ",
    ARRAYTOTEXT), BYROW(tk, LAMBDA(a, COUNTA(TEXTSPLIT(a, ",")))))), ", ",, TRUE), 3), 6),
  --TOCOL(TEXTSPLIT(TEXTJOIN(";",, tk), ",", ";"), 3),
    TOCOL(TEXTSPLIT(TEXTJOIN(";",, TAKE(t,, -1)), ",", ";"), 3)))
)

Hope this helps.

IlirU

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.
Data Formatting.xlsx22 KB
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.

Data formatting.xlsx23 KB
- 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
  - PeterBartholomew1
    Silver Contributor
    Mar 12, 2026
    anupambit1797
    UNPIVOT.CSVλ is simply a defined name I used to reference the Lambda function. The MAPλ helper function is posted on my GitHub repository
    https://gist.github.com/pbartxl/a14b250985da31be843ce9ff35d888fc
    What it does is to allow the in-built MAP function to return arrays of arrays (without introducing additional lambda functions to reference the inner arrays you would be limited to arrays of scalars which is not that much use)!
    OliverScheurich
    Since I found myself implementing unpivot functionality it would probably have made sense to consider a more overt database strategy! 😆

Forum Discussion

Data Formatting