Excel-在一个列中查找重复项，然后将数量求和到另一列中? [英] Excel - Find duplicates in one column then sum quantities into another column?

问题描述

EDIT#1:

如果您可以接受VBA，请尝试:

 公共函数unikue(范围为rng)Dim arr，c作为集合，r作为范围昏暗nCall只要长，nColl只要长昏暗的我只要设置c =新收藏nCall = Application.Caller.Count关于错误继续对于每一次c.添加r.Text，CStr(r.Text)下一个出错时转到0nColl = c.Count如果nCall>然后nCollReDim arr(1到nCall，1到1)对于i = 1进行nCallarr(i，1)="接下来我别的ReDim arr(1到nColl，1到1)万一对于i = 1到nCollarr(i，1)= c.Item(i)接下来我unikue = arr结束功能 

Find duplicates in one column then sum quantities into another column

DATA       RESULT   

A  1       A  11
A  1       B  7
A  9       C  5
B  2       D  4
B  2       E  8
B  3            
C  5            
D  4            
E  7            
E  1    

解决方案

For EXCEL 365 (or the Excel web app):

In C1 enter:

=UNIQUE(A1:A10)

in D1 enter:

=SUMIF(A1:A10,C1#,B1:B10)

EDIT#1:

If VBA is acceptable to you, then try:

Public Function unikue(rng As Range)
    Dim arr, c As Collection, r As Range
    Dim nCall As Long, nColl As Long
    Dim i As Long
    Set c = New Collection
    
    nCall = Application.Caller.Count
    
    On Error Resume Next
        For Each r In rng
            c.Add r.Text, CStr(r.Text)
        Next r
    On Error GoTo 0
    nColl = c.Count
    
    
    If nCall > nColl Then
        ReDim arr(1 To nCall, 1 To 1)
        For i = 1 To nCall
            arr(i, 1) = ""
        Next i
    Else
        ReDim arr(1 To nColl, 1 To 1)
    End If
    
    For i = 1 To nColl
        arr(i, 1) = c.Item(i)
    Next i
    
    unikue = arr
End Function

这篇关于Excel-在一个列中查找重复项，然后将数量求和到另一列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！