Home Articles

当“A”和“B”的值与前一行匹配时,组合“C”的出现

Asked
Viewed 736 times
0

真的希望你能提供帮助,因为我对这个问题视而不见 . 我的电子表格有20列,大约20,000行 . 除了1列之外,所有行都是重复的,我需要连接那些行匹配的1列中的所有变量 . 我的数据是有序的,所以所有重复的行都在一起,目前我有一个宏,但必须手动选择一个范围,这很慢 .

我的问题是,我可以运行一个VBA /公式来匹配整个工作表中的每个重复的部分行,然后连接这些行的唯一单元格吗?请看我的简化示例 .

simple example

1 Answer

  • 1

    这段代码应该可以胜任;我确定这个问题将因为偏离主题而被关闭(或者迁移到Stack Overflow,它实际上属于它):

    Sub GatherCountries()
    '''Subroutine to loop through rows of column A and concatenate data of column C to column D,
    '''when row - 1 == row
    
    'Declare local variable types
    Dim worksheetName As String
    Dim rowNumber, rowEndNumber As Integer
    Dim pasteCell As Range
    
    'Declare local variables
    worksheetName = ActiveSheet.Name
    rowEndNumber = FindLastRow(worksheetName) 'Function call to function defined below
    
    'Loop through each row, starting at line 2 as header is line 1
        For rowNumber = 2 To rowEndNumber
            With Worksheets(worksheetName) 'Reduce amount of unnecessary repitition
                'Case where cell Ax and Bx equal Ax-1 and Bx-1
                If .Cells(rowNumber, 1) = .Cells(rowNumber - 1, 1) And _
                .Cells(rowNumber, 2) = .Cells(rowNumber - 1, 2) Then
                    pasteCell.Value = pasteCell.Value & ", " & .Cells(rowNumber, 3) 'Concatenate country to existing string
                'Case where cell Ax and Bx does not equal Ax-1 and Bx-1, loop will always enter this first
                Else
                    Set pasteCell = .Cells(rowNumber, 4) 'Set the cell where concatenation should take place
                    pasteCell.Value = .Cells(rowNumber, 3) 'Populate concatenation cell with first entry of country
                End If
            End With
        Next rowNumber
    End Sub
    
    Function FindLastRow(ByVal SheetName As String) As Long
    '''Function to return the last row number of column A
    
        Dim WS As Worksheet
    
        On Error Resume Next
        Set WS = ActiveWorkbook.Worksheets(SheetName)
        FindLastRow = WS.Cells.Find(What:="*", After:=WS.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        On Error GoTo 0
    
    End Function
    

    使用更多代码,可以查找哪些列包含要检查的值和要连接的值,但考虑到问题中示例的结构,这将按照要求执行:

    输入:

    CountriesGatheringExcelInput

    代码运行后的输出:

    CountriesGatheringExcelOutput

Related