Search code examples
excelexcel-formulaexcel-365

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.

Solution

  • 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))