want to covert data from columns to row as per attached details

[mahesh.mohite]

I want to capture start date and end date in single row for duplicate names


Name Start Date End Date
Aditya 01-Jul-23 30-Sep-23
Aditya 01-Oct-23 31-Dec-23
Aditya 01-Jan-24 31-Mar-24
Aditya 01-Apr-24 30-Jun-24
Aditya 01-Jul-24 30-Sep-24
Rajesh 26-Aug-23 30-Sep-23
Rajesh 01-Oct-23 31-Dec-23
Rajesh 01-Jan-24 31-Mar-24
Rajesh 01-Apr-24 30-Jun-24
Rajesh 01-Jul-24 07-Jul-24
Pavan 01-Jul-23 30-Sep-23
Pavan 01-Oct-23 31-Dec-23

[TMS]

There will be a better way, but, for the moment, try:

H8:

=UNIQUE(A5:A37)

I8, copied down:

=LET(tr,TOROW(FILTER($B$5:$D$37,$A$5:$A$37=H8)),IF(tr="","",tr))

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type text}, {"End Date", type text}}, "lt-LT"),{"Start Date", "End Date"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Name"}, {{"Count", each _, type table [Name=text, Merged=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Merged]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "-"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type date}, {"Custom.2", type date}, {"Custom.3", type date}, {"Custom.4", type date}, {"Custom.5", type date}, {"Custom.6", type date}, {"Custom.7", type date}, {"Custom.8", type date}, {"Custom.9", type date}, {"Custom.10", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"v

[AliGW]

All in one spill formula:

=LET(u,UNIQUE(A5:A37),tr,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TOROW(FILTER($B$5:$D$37,$A$5:$A$37=y))))),1),HSTACK(u,IFNA(IF(tr=0,"",tr),"")))

[djclements]

Another possible workaround to output an "array of arrays" (incl. headers) with MAP could be:

=LET(
    hdr, HSTACK(B4:C4, ""),
    unq, UNIQUE(TOCOL(A5:A1000, 1)),
    arr, MAP(unq, LAMBDA(v, TEXTJOIN("|", 0, "", FILTER(B5:D1000, A5:A1000=v), ""))),
    num, MAX(LEN(arr) - LEN(SUBSTITUTE(arr, "|", ))) - 1,
    VSTACK(
        HSTACK("Name", TOROW(IF(SEQUENCE(num/COLUMNS(hdr)), hdr))),
        HSTACK(unq, IFERROR(--TEXTBEFORE(TEXTAFTER(arr, "|", SEQUENCE(, num)), "|"), ""))
    )
)

See attached, if needed...