在列中查找重复项，然后从另一列中添加其对应的值 [英] Find duplicates in a column and add their corresponding values from another column

问题描述

我在A列中有员工ID和工作小时数.

I have column A with staff ids and hours worked in column K.

如果一个工作人员ID出现不止一次，我想增加工作时间并将结果放在与该工作人员ID的第一个实例相对应的另一列中，并且重复项为0.

I would like if a staff id appears more than once to add hours worked and put the result in another column corresponding to the first instance of that staff id and the duplicates being 0.

这是一份月度报告，随时可能有超过2k条记录.

This is for a monthly report and there may be over 2k records at any point.

推荐答案

正如其他所有人所说，数据透视表确实是最好的方法.如果不确定如何使用数据透视表或它有什么用处，请

As everyone else said, a Pivot Table really is the best way. If you're unsure how to use a PivotTable or what it's good for, refer to this SO post where I explain in detail.

无论如何，我整理了以下VBA函数以帮助您入门.这绝不是最有效的方法.它还做出以下假设:

Anyway, I put together the below VBA function to help get you started. It's by no means the most efficient approach; it also makes the following assumptions:

• Sheet 1拥有所有数据
• A具有员工ID
• B有营业时间
• C被保留用于总小时数
• D将可用于处理状态输出

• Sheet 1 has all the data
• A has Staff Id
• B has Hours
• C is reserved for Total Hours
• D will be available for processing status output

当然，只需稍微更改一下代码，就可以轻松更改所有内容.查看代码，对它进行注释以供您理解.

This of course can all be changed very easily by altering the code a bit. Review the code, it's commented for you to understand.

Status列必须存在的原因是为了避免处理已经处理过的Staff Id.您可以非常修改代码以避免使用此专栏，但这就是我处理问题的方式.

The reason a Status column must exist is to avoid processing a Staff Id that was already processed. You could very alter the code to avoid the need for this column, but this is the way I went about things.

代码

Public Sub HoursForEmployeeById()

    Dim currentStaffId As String
    Dim totalHours As Double

    Dim totalStaffRows As Integer
    Dim currentStaffRow As Integer
    Dim totalSearchRows As Integer
    Dim currentSearchRow As Integer

    Dim staffColumn As Integer
    Dim hoursColumn As Integer
    Dim totalHoursColumn As Integer
    Dim statusColumn As Integer

    'change these to appropriate columns
    staffColumn = 1
    hoursColumn = 2
    totalHoursColumn = 3
    statusColumn = 4

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    totalStaffRows = Sheet1.Cells(Rows.Count, staffColumn).End(xlUp).Row
    For currentStaffRow = 2 To totalStaffRows
        currentStaffId = Cells(currentStaffRow, staffColumn).Value

        'if the current staff Id was not already processed (duplicate record)
        If Not StrComp("Duplicate", Cells(currentStaffRow, statusColumn).Value, vbTextCompare) = 0 Then
            'get this rows total hours
            totalHours = CDbl(Cells(currentStaffRow, hoursColumn).Value)
            'search all subsequent rows for duplicates
            totalSearchRows = totalStaffRows - currentStaffRow + 1
            For currentSearchRow = currentStaffRow + 1 To totalSearchRows
                If StrComp(currentStaffId, Cells(currentSearchRow, staffColumn), vbTextCompare) = 0 Then
                    'duplicate found: log the hours worked, set them to 0, then mark as Duplicate
                    totalHours = totalHours + CDbl(Cells(currentSearchRow, hoursColumn).Value)
                    Cells(currentSearchRow, hoursColumn).Value = 0
                    Cells(currentSearchRow, statusColumn).Value = "Duplicate"
                End If
            Next
            'output total hours worked and mark as Processed
            Cells(currentStaffRow, totalHoursColumn).Value = totalHours
            Cells(currentStaffRow, statusColumn).Value = "Processed"
            totalHours = 0  'reset total hours worked
        End If
    Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic

End Sub

之前

之后

这篇关于在列中查找重复项，然后从另一列中添加其对应的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！