Look up data in excel dataset and return column headers

asked 2021-04-24T21:05:30.383+00:00
TB 21 Reputation points

In Excel, I have multiple column headers and below each header are items belong to that column header. For instance, it has multiple of server names as the column headers and underneath are the software names listed.

I'd like to build a formula to look up for a name of software and returns the name of the server name or returns multiple server names, since one software name can be installed on multiple servers.

How do I go about doing that with formulas or vbscript in Excel?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,334 questions
No comments
{count} votes

4 additional answers

Sort by: Most helpful
  1. answered 2021-04-25T03:01:28.637+00:00
    TB 21 Reputation points

    This is the exact output results I was looking for.

    Can you walk me through how you were able to achieve this using Power Query?


  2. answered 2021-04-25T09:31:14.427+00:00
    Lz._ 8,801 Reputation points

    @TB

    A variation with dynamic number of columns and names. Assuming data in Excel Table1:

    let  
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
        ChangedTypes = Table.TransformColumnTypes(Source,  
            List.Transform(Table.ColumnNames(Source), each {_, type text})  
        ),  
        UnpivotedAllColumns = Table.UnpivotOtherColumns(ChangedTypes, {}, "Server", "SW"),  
        ConcatServers = Table.Group(UnpivotedAllColumns, {"SW"},  
            {"Servers", each Text.Combine(List.Sort([Server]), ", "), type text}  
        )  
    in  
        ConcatServers  
    
    No comments

  3. answered 2021-04-26T07:48:12.137+00:00
    Lz._ 8,801 Reputation points

    @TB

    Herbert's proposal is a good one. A few suggested adjustments:

    let  
      Source = ...,  
      Types = ...,  
      
      // Edited the step to replace the default column names (Attribute & Value)  
      UnPivot = Table.UnpivotOtherColumns(Types, {}, "Server", "SW"),  
      Sort = Table.Buffer(  
        // Removed 2nd sorting key (useless)  
        Table.Sort(UnPivot, {{"Server", Order.Ascending}})  
      ),  
      Concat = Table.Group(Sort,{"SW"},  
        // Added ", type text" to the aggregation  
        {"Servers", each Text.Combine([Server],", "), type text}  
      )  
    in  
      Concat  
    
    No comments

  4. answered 2021-04-27T16:51:48.387+00:00
    TB 21 Reputation points

    Thank you: HerbertSeidenberg-6295 and Lz-3068 for your help.

    I was able to crack and got my list to sort just like you guys showed and scripted.

    You guys are awesome. :)

    Graciously thank you both of you,

    Cheers