vba - 根据两个不同的 ID 合并行

Question

我希望根据两个不同的列对表格进行排序。

这就是我所拥有的:

| EAN | album_id | photo     |
|-----|----------|-----------|
| 111 | 123      | 64.jpg    |
| 111 | 123      | 65.jpg    |
| 222 | 123      | 64.jpg    |
| 222 | 123      | 65.jpg    |

这是期望的结果:

| EAN | album_id | photo          | primary |
|-----|----------|----------------|---------|
| 111 | 123      | 64.jpg, 65.jpg | 1       |
| 222 | 123      | 64.jpg, 65.jpg | 0       |

这是我正在使用的原始代码(我根据自己的具体需要对其进行了更改)，到目前为止，它仅处理 album_id 和照片排序，但不处理 EAN 或主列:

Sub merge()
Dim LR As Long, Delim As String

'Allow user to set the Delimiter
    Delim = Application.InputBox("Merge column B values with what delimiter?", "Delimiter", "|", Type:=2)
    If Delim = "False" Then Exit Sub
    If Delim = "" Then
        If MsgBox("You chose a blank delimiter, this will merge column B value into a single continuous string. Proceed?", _
            vbYesNo, "Merge with no delimiter") = vbNo Then Exit Sub
    End If

'Sort data
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

'Concatenate column B values so last matching row in each group has all values
    With Range("E2:E" & LR)
        .FormulaR1C1 = "=IF(RC1=R[-1]C1, R[-1]C & " & """" & Delim & """" & " & RC2, RC2)"
        .Value = .Value
        .Copy Range("B2")
        .FormulaR1C1 = "=IF(RC1=R[1]C1, """", 1)"
        Range("E:E").AutoFilter 1, "<>1"
        .EntireRow.Delete xlShiftUp
        .EntireColumn.Clear
    End With
ActiveSheet.AutoFilterMode = False
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

如何更改代码(部分或全部)以实现我正在寻找的最终结果？

非常感谢您为解决此问题提供的任何帮助。

Answer 1

您将要删除行，因此首先排序，然后从下往上进行操作。

dim i as long, delim as string

delim = ", "

with worksheets("sheet1")
    with .cells(1, 1).currentregion
        .cells.sort Key1:=.Columns(1), Order1:=xlAscending, _
                    Key2:=.Columns(2), Order2:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlyes
        for i = .rows.count -1  to 2 step -1
            if .cells(i, "A").value = .cells(i+1, "A").value and _
               .cells(i, "B").value = .cells(i+1, "B").value then
                .cells(i, "C").value = .cells(i, "C").value & delim & .cells(i+1, "C").value
                .cells(i+1, "A").entirerow.delete
                .cells(i, "D").value = abs(iserror(application.match(.cells(i, "B").value, .range(.cells(1, "B"),.cells(i-1, "B")), 0)))
            end if
        next i
    end with
end with