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