question

TB-1159 avatar image
0 Votes"
TB-1159 asked Lz-3068 commented

Look up data in excel dataset and return column headers

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?

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 avatar image
0 Votes"
TB-1159 answered HerbertSeidenberg-6295 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I defer to LZ's better solution.

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@TB-1159

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@TB-1159

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 avatar image
0 Votes"
TB-1159 answered Lz-3068 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Glad we could help @TB-1159 & Thanks for posting back. Take care...

0 Votes 0 ·