Dec 12 2018 01:08 AM
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?
Dec 12 2018 09:39 AM - edited Dec 12 2018 09:40 AM
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)
Dec 14 2018 08:30 AM
That could be done by Power Query
as in attached file
Dec 15 2018 07:22 AM
Thanks. I do not have Microsoft Excel 2010 sp1 so cannot install it. Any steps on how to do it after installing powerquery ?
Dec 15 2018 01:16 PM
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