Home

rows as columns with custom names

Sam55
Occasional Contributor

I have disk information of 100 servers in format like below:

 

ServernameDriveFreeDiskSpace(GB)Size(GB)
Server1C:460
Server1D:70200

 

I don't want the servername to appear twice or thrice in the row. Instead I want information for one server in a single row, like below:

 

ServernameC: FreeSpace(GB)C: Size(GB)D: FreeSpace(GB) D: Size(GB)
Server146070200

 

Like this there are 100 servers. How can I achieve this in excel?

4 Replies

Hi there!

 

You should use some pivot tables to automatize the mechanism.

 

In alternative, you can use the function SUMIF adding a column with the name of the server and the name of the disk in the same cell.

 

Briefly:

sheet1 -> disk information like the ones you have, adding a column on the right (and then hidden back to avoid graphic problems) with the formula =$A2&$B2 (and then use it for all the rows)

 

sheet2 -> the table like the one you want with all the names of the servers (Server1, Server2, ...Server100) and the same columns you made. The formulas should be:

cell B2: SUMIF(sheet1!$E$2:$E$102;$A2&"C:";sheet1!$C$2:$C$102)

cell C2: SUMIF(sheet1!$E$2:$E$102;$A2&"C:";sheet1!$D$2:$D$102)

cell D2: SUMIF(sheet1!$E$2:$E$102;$A2&"D:";sheet1!$C$2:$C$102)

cell E2: SUMIF(sheet1!$E$2:$E$102;$A2&"D:";sheet1!$D$2:$D$102)

That could be done by Power Query

image.png

as in attached file

Thanks.  I do not have Microsoft Excel 2010 sp1 so cannot install it. Any steps on how to do it after installing powerquery ?

You may start with that page https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de as an introduction, after that open the file attached to previous post and check step by step how query works

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies