Forum Discussion
rows as columns with custom names
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?
4 Replies
That could be done by Power Query
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
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)
