Excel VBA代码在列中查找重复项，并从其他列中添加相应的值 [英] Excel VBA code to find duplicates in a column and add their corresponding values from another column

问题描述

我希望有人能够帮助我。我有一个A列的工作人员的id，并在他们的小时工作在另一列K.有一些工作人员的id在列表中出现不止一次，我想知道是否有一种方法在Excel VBA中，我可以检查如果每个员工ID都出现不止一次，如果是，则将其工作时间加起来，并将结果放在与该员工ID的第一个对应的另一个列中，并且重复的值为0。

这是一个月度报告，任何时候可能有超过2k的记录。

任何帮助将不胜感激。 >

提前感谢

PS - 我只是VBA的中间人。

解决方案

正如其他人所说，数据透视表真的是最好的方法。如果您不确定如何使用数据透视表或其有用的内容，参考这个SO帖子，我详细解释一下。

无论如何，我把下面的VBA函数放在一起帮助你开始。这不是最有效的方法;它还作出以下假设：

• Sheet 1 具有所有数据


• A 有员工ID


• B 有小时


• C 保留总时数


• D 将可用于处理状态输出

这个当然可以通过改变代码来很容易地改变。

Status 列必须存在的原因是为了避免处理已经处理的员工ID 。你可以非常改变代码，以避免这个列的需要，但这是我所做的事情的方式。

代码

  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 
 
'将这些更改为适当的列
 staffColumn = 1 
 hoursColumn = 2 
 totalHoursColumn = 3 
 statusColumn = 4 
 
 Application.Calculation = xlCalculationManual 
 Application.ScreenUpdating = False 
 totalStaffRows = Sheet1.Cells（Rows.Count，staffColumn）.End（xlUp）.Row 
对于currentStaffRow = 2 To totalStaffRows 
 currentStaffId = Cells（currentStaffRow ，staffColumn）.Value 
 
'如果cur租金人员ID尚未处理（重复记录）
如果不是StrComp（Duplicate，Cells（currentStaffRow，statusColumn）.Value，vbTextCompare）= 0然后
'得到这个行总计小时
 totalHours = CDbl（Cells（currentStaffRow，hoursColumn）.Value）
'搜索所有后续行的副本
 totalSearchRows = totalStaffRows  -  currentStaffRow + 1 
对于currentSearchRow = currentStaffRow + 1 To totalSearchRows 
如果StrComp（currentStaffId，Cells（currentSearchRow，staffColumn），vbTextCompare）= 0然后
'重复发现：记录工作时间，设置为0，然后标记为Duplicate 
 totalHours = totalHours + CDbl （Cells（currentSearchRow，hoursColumn）.Value）
单元格（currentSearchRow，hoursColumn）.Value = 0 
单元格（currentSearchRow，statusColumn）.Value =重复
结束如果
下一个
'输出总工作时间并标记为已处理
单元格（currentStaffRow，totalHoursColumn）.Value = totalHours 
单元格（currentStaffRow，statusColumn）.Value =已处理
 totalHours = 0'重置总工作时间
结束如果
下一个
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationAutomatic 
 
 End Sub 
  

BEFORE

AFTER

I hope someone will be able to help me with this. I have a column A with staff id's and have their hours worked in another column K. There are some staff id's that appear more than once on the list and I would like to know if there is a way in Excel VBA with which I can check if each of the staff id's appears more than once, and if so, then add up their total hours worked and put the result in another column corresponding to the first instance of that staff id and the duplicates being 0.

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

Any help will be appreciated.

Thanks in advance

PS - I am just an intermediate when it comes to VBA.

解决方案

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.

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 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.

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.

CODE

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

BEFORE

AFTER

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