CODEWITHSUNDEEP
excelexcel-formulaexcel-365
user25652804
user25652804

Reputation: 23

Excel, generate a list of names depending on row and column values of another sheet

I have a similar excel sheet which has dates in Column A and Names in the Row 1. Each name has a value per day.

Date Alice Bob Charl Dan
01/01/24 E L E E
02/01/24 E L L A

I need another sheet that lists dates in the first column, value in second column and then that row list all names with corresponding values in the first sheet. Something like:

Date Value Name1 Name2 Name3
01/01/24 E Alice Charl Dan
01/01/24 L Bob
01/01/24 A
02/01/24 E Alice
02/01/24 L Bob Charl
02/01/24 A Dan

I'm using Microsoft excel 365

How can this be achieved? I can't find a excel formula that returns multiple values for a corresponding row. I've been going in circles for ages so any help is greatly appreciated.

Edit: I think Index and Aggregate get close to it, as shown here https://www.xelplus.com/return-multiple-match-values-in-excel/ but I can't quit figure it out.

Upvotes: 1

Views: 202

Answers (3)

nkalvi
nkalvi

Reputation: 2614

Power Query is better, but using formula for fun:

To view the result of each step, change final variable (unpivotted).

=LAMBDA(names, dates, values,
    LET(
        uniq_values, UNIQUE(TOCOL(values)),
        values_for_date, LAMBDA(date_,
            INDEX(values, XMATCH(date_, dates), )
        ),
        names_for_date_values, LAMBDA(date_, values,
            DROP(
                REDUCE(
                    "",
                    values,
                    LAMBDA(acc, value,
                        VSTACK(
                            acc,
                            FILTER(
                                names,
                                values_for_date(date_) = value,
                                ""
                            )
                        )
                    )
                ),
                1
            )
        ),
        format_date, LAMBDA(date_, TEXT(date_, "mm/dd/yyyy")),
        date_value_names, DROP(
            REDUCE(
                "",
                dates,
                LAMBDA(acc, date_,
                    VSTACK(
                        acc,
                        HSTACK(
                            EXPAND(
                                format_date(date_),
                                ROWS(uniq_values),
                                ,
                                format_date(date_)
                            ),
                            uniq_values,
                            names_for_date_values(
                                date_,
                                uniq_values
                            )
                        )
                    )
                )
            ),
            1
        ),
        header, HSTACK(
            "Date",
            "Value",
            "Name" & TOROW(SEQUENCE(COLUMNS(date_value_names) - 2))
        ),
        unpivotted, VSTACK(header, IFNA(date_value_names, "")),
        unpivotted
    )
)(B1:E1, A2:A3, B2:E3)

Formula and result

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54817

Transform Data: Unpivot

=LET(list,A1:E3,rlr_title,"Value",vals_title,"Name",
    shv,DROP(TAKE(list,1),,1),
    sd,DROP(list,1),
    sdr,ROWS(sd),
    sr,TAKE(sd,,1),
    sv,DROP(sd,,1),
    vu,UNIQUE(TOCOL(sv)),
    vur,ROWS(vu),
    vus,SEQUENCE(vur),
    drl,HSTACK(TOCOL(IF(SEQUENCE(,vur),sr)),
        TOCOL(IF(SEQUENCE(,sdr),vu),,1)),
    dv,IFNA(DROP(REDUCE("",SEQUENCE(sdr),LAMBDA(rr,r,LET(
        svr,CHOOSEROWS(sv,r),
        VSTACK(rr,DROP(REDUCE("",vus,LAMBDA(cc,c,
            VSTACK(cc,FILTER(shv,ISNUMBER(XMATCH(svr,
            CHOOSEROWS(vu,c))),"")))),1))))),1),""),
    dh,HSTACK(TAKE(list,1,1),rlr_title,vals_title&SEQUENCE(,COLUMNS(dv))),
    r,VSTACK(dh,HSTACK(drl,dv)),
    r)

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27263

Using Power Query it will be lot easier than using an Excel Formula:

enter image description here

  • Convert the ranges into Structured References aka Tables and name it as Table1`
  • Open a blank query from Data Tab --> Get Data --> From Other Sources --> Blank Query
  • From Home Tab --> Click on Advanced Editor --> delete anything what you see and paste the following:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DataType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Alice", type text}, {"Bob", type text}, {"Charl", type text}, {"Dan", type text}}),
    UnpivotOtherCols = Table.UnpivotOtherColumns(DataType, {"Date"}, "Name", "Value"),
    GroupBy = Table.Group(UnpivotOtherCols, {"Date", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Date=nullable date, Attribute=text, Value=text]}}),
    ExtractNames = Table.AddColumn(GroupBy, "Name", each [All][Name]),
    Expanded = Table.TransformColumns(ExtractNames, {"Name", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    SplitByDelim = Table.SplitColumn(Expanded, "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
    RemovedCols = Table.RemoveColumns(SplitByDelim,{"Count", "All"})
in
    RemovedCols
  • Now, below one can find a Done button, click on it.
  • From File --> Click on Close & Load or Close & Load To import data to Excel to get the desired output.
  • Now, with every addition of new data, just refresh the imported table to get the updated results.

Using Excel Formulas:

enter image description here

• MAKEARRAY():

=LET(
     _Data, Table1[#All],
     _Names, DROP(TAKE(_Data,1),,1),
     _Vals, UNIQUE(TOCOL(DROP(_Data,1,1))),
     _Date, DROP(TAKE(_Data,,1),1),
     _DateExpanded, TOCOL(IFNA(EXPAND(_Date,,ROWS(_Vals)),_Date)),
     _ValsExpanded, TOCOL(IFNA(EXPAND(_Vals,,ROWS(_Date)),_Vals),,1),
     _DataBody, MAKEARRAY(ROWS(G2:G7),ROWS(_Vals),LAMBDA(r,c,
            INDEX(TOROW(IFS((INDEX(_DateExpanded,r)=_Date)*
            (INDEX(_ValsExpanded,r)=DROP(_Data,1,1)),_Names),2),c))),
     _Output, IFERROR(HSTACK(_DateExpanded,_ValsExpanded, _DataBody),""),
 VSTACK(HSTACK("Date","Value","Name"&SEQUENCE(,ROWS(_Vals))),_Output))

• REDUCE():

=LET(
     _Data, Table1[#All],
     _Vals, UNIQUE(TOCOL(DROP(_Data,1,1),3)),
     _Output, IFNA(DROP(REDUCE("",SEQUENCE(ROWS(_Data)-1),LAMBDA(a,b,
          VSTACK(a, DROP(REDUCE("",_Vals,LAMBDA(c,d,
          LET(e, CHOOSEROWS(DROP(_Data,1),b), f, TAKE(e,,1),
          VSTACK(c,HSTACK(f,d,IFERROR(FILTER(DROP(TAKE(_Data,1),,1),DROP(e,,1)=d),"")))))),1)))),1),""),
      VSTACK(HSTACK(A2,"Value","Name"&SEQUENCE(,ROWS(_Vals))),_Output))

Upvotes: 3

Related Questions