How 2 sum all the values in col B that same in col a

[beersamurai]

I have a bunch of servers with their share allocation and utilization and I want to sum the totals for each server rather than report on them by share name.

IE:

Hostname: Allocated Used Percentage
ServerA:/U01/ 100 50 50%
ServerA:/U02/ 200 5o 25%
ServerA:/U02/
..
..
ServerZ:/U99/
ServerZ:/U100/

I've tried a few things, Dbase, Vlookup, Sumif, etc...........any help would be appreciative. If this is already posted here I must not have typed in a good enough description 'cause I couldn't find anything exactly and the stuff I did try isn't working.

And if any one has a good doc on pivot tables I would be interested in taking a look.

Thanks...

 

[Aladin Akyurek]

What are the desired results, given the sample you provided?

 

[beersamurai]

I would like to end up with all unique records for the hosts with just the total allocation, used gigabytes and percentage listed in the columns next to each.

Is there a grouping function that would then sum all the values?

For the percentage column I was just planning on doing that last and take the newly calculated totals and divide 'em.

Thanks the response.
.

 

[jindon]

Hi

assuming headings are on the 1st row from col.A. to col.D
result will be displayed from col G to col.J

Sub sum_all()
Dim dic As Object, lastR As Long, lastr2 As Long, x
Set dic = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
With ActiveSheet
    .Columns("g:j").Clear
    lastR = .Range("a65536").End(xlUp).Row
    For i = 2 To lastR
        If Not dic.exists(.Cells(i, 1).Value) Then
            dic.Add .Cells(i, 1).Value, Nothing
        End If
    Next
    x = dic.keys
    For i = LBound(x) To UBound(x)
        .Cells(i + 2, "G").Value = x(i)
    Next
    .Range("a1").Resize(1, 4).Copy Destination:=.Range("g1")
    .Cells(2, "h").FormulaR1C1 = "=sumif(c1:c1,rc7,c2:c2)"
    .Cells(2, "i").FormulaR1C1 = "=sumif(c1:c1,rc7,c3:c3)"
    .Cells(2, "j").FormulaR1C1 = "=sumif(c1:c1,rc7,c4:c4)"
    lastr2 = .Range("g65536").End(xlUp).Row
    .Range("h2:j2").AutoFill Destination:=.Range("h2:j" & lastr2)
    .Columns("j").NumberFormat = "0.0%"
    .Columns("g:j").AutoFit
    With .Range("g1:j" & lastr2)
        .BorderAround Weight:=xlThin
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideVertical).Weight = xlThin
    End With
End With
Application.ScreenUpdating = True
Set dic = Nothing
Erase x
End Sub

hope this helps

jindon

 

[fairwinds]

Hi,

Sounds like a pivot table would be ideal for this. Did you try?

 

[beersamurai]

Thanks very much for help. I was looking to be in some hot water if I didn't have these reports done by this morning so thanks again!

 

[beersamurai]

Fairwinds, I did try a pivot table, probably a good few hours of trying. I just don't know pivot tables that well and it wasn't working for me. Let me know if you can get it to work. I'd be curious. Thanks.

 

[fairwinds]

I do not think I could explain that better that the pivot table wizzard.

Just follow the instructions at each step and use help when needed. It's easy, I promise.