I have disk information of 100 servers in format like below:
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:
Like this there are 100 servers. How can I achieve this in excel?
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.
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
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