将两列中的相同数据对齐，同时保留excel中第3列中的值 [英] Align identical data in two columns while preserving values in the 3rd in excel

问题描述

在excel的工作表中，我有大量的数据在整个3列中传播。我想在A& A列中匹配相同的值同时保持与列B相关联的值与它们各自的B组件一致。这是一个例子：

I have a large amount of data spread throughout 3 columns on a worksheet in excel. I want to match identical values in columns A & B while keeping values associated with column B inline with their respective B components. Here is an example:

我有什么 -

    A   B   C
1   a   g   '
2   b   h   *
3   c   a   ?
4   d   e   $
5   e   b   /
6   f   j   )
7   g   c   #
8   h   d   @
9   i
10  j

我想要实现的目标：

    A   B   C
1   a   a   ?
2   b   b   /
3   c   c   #
4   d   d   @
5   e   e   $
6   f
7   g   g   '
8   h   h   *
9   i
10  j   j   )

我发现这个代码，但它不也会携带第3列值。

I found this code, but it doesn't also carry over the 3rd column values.

Sub Macro1()
    Dim rng1 As Range
    Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
    rng1.Offset(0, 1).Columns.Insert
    With rng1.Offset(0, 1)
        .FormulaR1C1 = _
        "=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
        .Value = .Value
    End With
End Sub

任何帮助将不胜感激！
谢谢

Any help would be greatly appreciated! Thanks

推荐答案

你将不得不将值存储在内存中（一个变体数组似乎合适），然后清除价值观，并通过A1：A10寻找与阵列的第一个排名的匹配。

You will have to store the values in memory (a variant array seems appropriate), then clear the values and work through A1:A10 looking for matches to the first rank of the array.

Sub aaMacro1()
    Dim i As Long, j As Long, lr As Long, vVALs As Variant
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        vVALs = Range("B1:C" & lr)
        Range("B1:C" & lr).ClearContents
        For i = 1 To lr
            For j = 1 To UBound(vVALs, 1)
                If vVALs(j, 1) = .Cells(i, 1).Value Then
                    .Cells(i, 2).Resize(1, 2) = Application.Index(vVALs, j)
                    Exit For
                End If
            Next j
        Next i
    End With
End Sub

如果您在数据副本上进行了测试，那可能是最好的在返回之前从B1：C10中删除值。

It would probably be best if you tested that on a copy of your data as it does remove the values from B1:C10 before returning them.

这篇关于将两列中的相同数据对齐，同时保留excel中第3列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！