Return and non-duplicate SUMIF data

**marreco** · 05-22-2012, 07:42 AM

How could I have seen VBA, the data in column "A" no duplicates in the column "E" and the sum of these criteria in column "F".

See Attachment

Thank's

**tom1977** · 05-22-2012, 07:51 AM

Hi use builtin function of excel in VAB

Sub aa()
[f2] = Application.WorksheetFunction.SumIf([a2:a26], [e2], [b2:b26])
End Sub

I think You can adjust it to your needs

**marreco** · 05-22-2012, 07:56 AM

Not so.

the code needs to go céluas of the column a "A" and see how many I have different data.

then he sends this reultado to column "E" is the code I have found that 3 data only ones he will return me the total of each column as one "F" under the column "E".

**nilem** · 05-22-2012, 08:00 AM

option

Sub ertert()
Dim x, y(), i&, j&, k&, n&, s$

x = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim y(1 To UBound(x), 1 To 2)

On Error Resume Next
With New Collection
    For i = 1 To UBound(x)
        s = Trim$(x(i, 1))
        If Len(s) Then
            If IsEmpty(.Item(s)) Then
                k = k + 1: y(k, 1) = s: y(k, 2) = x(i, 2)
                .Add k, s
            Else
                n = .Item(s): y(n, 2) = y(n, 2) + x(i, 2)
            End If
        End If
    Next i
End With

If k > 1 Then [e2:f2].Resize(k).Value = y
End Sub

**marreco** · 05-22-2012, 08:06 AM

It was great!

But is giving the end of the column "E" value of 633.4

in my case I do not have value.
can eliminate this part?
it can also comment on the code, I am grateful

**tom1977** · 05-22-2012, 08:11 AM

try this

Sub bb()
Dim lasta, laste As Long
Dim nam, nam1, c As Range
lasta = Cells(Rows.Count, 1).End(xlUp).Row
Set nam = Range("a1:a" & lasta)
nam.Copy [e1]
nam.Offset(0, 4).RemoveDuplicates Columns:=1, Header:=xlYes
  laste = Cells(Rows.Count, 5).End(xlUp).Row
  Set nam1 = Range("e2:e" & laste)
  For Each c In nam1
  c.Offset(, 1) = Application.WorksheetFunction.SumIf(nam, c, nam.Offset(0, 1))
  Next
  
End Sub

works only with ex>2007

**tom1977** · 05-22-2012, 08:13 AM

Look into your file column A row 64

**marreco** · 05-22-2012, 08:15 AM

Thank you!!!

thank you both!

Thank you very much!

**Marcol** · 05-22-2012, 08:15 AM

Why do you want VBa for this?
In E2

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$1000,$E$1:$E1,0)),0),0))))

In F2

=IF(E2="","",SUMIF($A$2:$A$1000,E2,$B$2:$B$1000))

Drag/Fill both Down

**marreco** · 05-22-2012, 08:19 AM

If anyone can comment codes be very grateful!

nilem could you comment your code?

Return and non-duplicate SUMIF data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUM data

Re: Return and non-duplicate SUM data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Re: Return and non-duplicate SUMIF data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions