rows as columns with custom names

[Sam55]

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

Servername	Drive	FreeDiskSpace(GB)	Size(GB)
Server1	C:	4	60
Server1	D:	70	200

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:

Servername	C: FreeSpace(GB)	C: Size(GB)	D: FreeSpace(GB)	D: Size(GB)
Server1	4	60	70	200

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

[#2]

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)

[#3]

That could be done by Power Query

as in attached file

[#4]

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

[#5]

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