如何将Excel列表转换为数组?

I have a two column list of key-value pairs. For example:

我有一个键值对的两列列表。例如:

71  Archaebacteria
71  Mendosicutes
71  Metabacteria
72  blue green algae
72  blue-green algae
72  Cyanochloronta
72  Cyanophyta
73  CFB group
73  Chloroflecales
73  Chloroflexaceae/Deinococcaceae group
73  Chloroflexus/Deinococcaceae group
73  Chloroflexus/Deinococcus group
73  Cytophaga-Flexibacter-Bacteroides group
73  Fibrobacter-Acidobacteria group
73  Flexistipes group
73  GNS bacteria
73  Green non-sulfur bacteria
73  true bacteria

I need to 'flatten' the two column list into an array where the key remains in column 1 and all values for the key are moved to new cells on the same row.

我需要将两个列列表“平坦化”到一个数组中，其中键保持在第1列，键的所有值都被移动到同一行的新单元格中。

Some keys have 20 values. For that case, there would be 21 columns (1 for the key, 20 for the key's values).

有些键有20个值。对于这种情况，将有21列(1为键值，20为键值)。

I have studied this question but have not been able to successfully modify it to produce the desired results.

我已经研究过这个问题，但还没能成功地修改它以产生预期的结果。

How to transform an Excel list into an array?

如何将Excel列表转换为数组?

Notes: a solution may leave a value in column 2 and 'copy' that value to the appropriate row in the array. Alternatively, a solution may 'move' the value to the proper position in the array, leaving a key with a NULL value. These "clean-up" conditions are OK as I'll sort/delete to remove detritus from the new array.

注意:解决方案可以在第2列中保留一个值，并将该值“复制”到数组中的相应行。另一种方法是，解决方案可以将值“移动”到数组中的适当位置，将键保留为空值。这些“清理”条件是可以的，因为我将排序/删除以删除新数组中的碎屑。

Either a formula or VBA solution is good.

无论是公式还是VBA解决方案都是好的。

1 个解决方案

#1

The code below uses Dictionary to organize all unique keys (71, 72, 73) and their values from Column A:B.

下面的代码使用Dictionary来组织所有惟一的键(71、72、73)以及它们在A:B列中的值。

Afterwards it pastes the values to Columns C:D.

然后将值粘贴到C:D列。

Code

代码

Option Explicit

Sub TestDict()

Dim Dic As Object
Dim C As Range, C2 As Range, lRow As Long
Dim Names As String, ID$, Key As Variant, KeyVal As Variant, IDVal As Variant

Set Dic = CreateObject("Scripting.Dictionary")

With Sheets("Sheet2") '<-- modify "Sheet2" with your sheet's name
    lRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For Each C In .Range("A1:A" & lRow).Cells
        If Not Dic.exists(CStr(C.Value)) Then
            ID = C.Value

            For Each C2 In .Range("A1:A" & lRow).Cells
                If C2.Value = ID Then
                    If Names = "" Then ' first key value
                        Names = C2.Offset(, 1).Value
                    Else  ' second and up key value
                        Names = Names & "," & C2.Offset(, 1).Value
                    End If
                End If
            Next C2
            Dic.Add ID, Names
        End If

        ID = Empty: Names = Empty
    Next C
End With

lRow = 1
With Sheets("Sheet2") ' <-- paste the organized dictionary key anv values the columns C:D
    For Each Key In Dic.Keys
        .Range("C" & lRow).Value = Key

        ' splitting values from "Merged" string Key to array
        KeyVal = Split(Dic(Key), ",")
        .Range("D" & lRow).Resize(1, UBound(KeyVal) + 1).Value = KeyVal
        lRow = lRow + 1
    Next Key
End With

End Sub

> Blockquote