I do a lot of backup system report digesting for my analyses , and sometimes you get a column in the text report that helpfully gives you mixed units – but at least it tells you the units.
Once in excel this can be a pain, so here is how I slay that beast. Drop a comment if there is a more elegant solution – Im sure there is.
Sometimes you get a report that has mixed output. CommVault has a checkbox to unify this but sometimes customers don’t check it, or sometimes TSM or NetBackup don’t have that option and you get output like this in a backup/storage report
|Size on Media|
As you can see, the units are there for all to see, but not useful in calculations or in sums or pivot tables.
Here’s a nice trick to sort these
- Insert a column to the right of the one with the values
- Select the column (in this case whole column size on media
- Go to the Data tab in excel, and then select “Text to column”
- In the dialog, go delimited, and use space, tab or whatever your report has in between those letters – be careful not to bung up the headers or other rows with errant delimiters.
- You should have now 2 columns
|Size On Media|
Now for the conversion.
- Insert a third column to the right of the units one you just made.
- Now use this formula in N2 (in this case) to get the GB converted to MB (or vice versa if you want to go the other way)
For the picture challenged the formula in N2 is:
= IF( M=”GB”, L2, L2/1024)
- Now you have a converted to MB Colum ( N in the case above)
- Select the formula, and then do a fill-down to populate the remaining entries in your report.
- Now select that calculated value column, copy it and then paste special (paste only the values) back into your original column. Voila – a unified GB value column. Adjust the decimals as needed.
|Size on Media|
- You can now remove the second two columns you used for the calculation – they are no longer needed (the unit and the calculation – which is now probably showing new data based on your paste-values anyway)
- Adjust your header to make sure units are shown as GB, or whatever your conversions had it as.
|Size on Media (GB)|
Now you can slice and dice the data without those nasty units getting in the way.
If there is a simpler way, Id be glad to hear it. You can probably do some of this with a data validation macro, but this is quick and dirty and gets it done.