计算列中的唯一数据，然后将其与另一个唯一数据进行匹配 [英] Counting unique data in a column and then matching it with another unique data

问题描述

这是一个令人尴尬的简单问题，但我无法找到一个方法来解决几个小时，也没有从网上搜索找到一个想法。

假设我在Excel工作表（表1）中有以下数据（实际数据可以是数千或数百万）：

  Name No ID 
 A A1 
 B4 
 B5 
 C C0 
 D  -  
 A A1 
 A A2 
 E  - 
 F  -  
 C C0 
 B B5 
 B B5 
 B B5 
 B6 
 A A1 
 A A1 
 A A2 
 B B3 
 B B3 
 B B3 
  

上面第（ID）列中的字符（ - ）也可以是数字0或空格。

我想要格式化上述数据（表2 ）

 姓名身份证号码
 A 2 
 B 4 
 C 1 
 D 0 
 E 0 
 F 0 
  

A有2个ID（A1和A2），B有4个ID（B1，B2，B3和B4），C有1个ID（C0），D，E和F没有ID。

使用Pivot Table可以获得最好的效果，就像这样

如何在MS Excel中执行类似表2的任务？如果可能的话使用VBA脚本。

解决方案

如果有人对上述问题感兴趣的VBA解决方案： b
$ b pre $ Sub $ GetUniqueCount（）
Dim dict1 As Object，dict2 As Object
Dim c1 As Variant，c2 As Variant
Dim i As Long，lastRow As Long，count As Long

Set dict1 = CreateObject（Scripting.Dictionary）
Set dict2 = CreateObject（Scripting.Dictionary）

lastRow = Cells（Rows.count，A）。End（xlUp）.Row
c1 = Range（A2：A& lastRow）
For i = 1 To UBound（c1，1）
dict1（c1（i，1））= 1
下一个i
范围（D2）Resize（dict1.count）= Application.Transpose（dict1 .keys）

lastRow = Cells（Rows.count，B）。End（xlUp）.Row
c2 = Range（B2：B& lastRow）
对于i = 1到UBound（c2，1）
dict2（c2（i，1））= 1
下一个我

对于每个单元格在范围D& Cells（Rows.count，D）。End（xlUp）.Ro w）
count = 0
For Each k2 In dict2.keys
If K2 Like cel.Value& *然后
count = count + 1
结束如果
下一个k2
cel.Offset（0，1）.Value = count
下一步cel
结束Sub

This is an embarrassingly simple question, but I was not able to find a way to figure it out for hours nor also to find an idea from online search.

Suppose I have the following data (the actual data can be thousands or millions) in Excel sheet (Table 1):

Name    No. ID
A       A1
B       B4
B       B5
C       C0
D       -
A       A1
A       A2
E       -
F       -
C       C0
B       B5
B       B5
B       B5
B       B6
A       A1
A       A1
A       A2
B       B3
B       B3
B       B3

The character (-) in column No. ID above can be also a number 0 or a blank cell.

I want to format the above data as follow (Table 2)

Name    Number of ID
A       2
B       4
C       1
D       0
E       0
F       0

It means A has 2 IDs (A1 and A2), B has 4 IDs (B1, B2, B3, and B4), C has 1 ID (C0), and D, E, and F have no ID.

The best I can get by using Pivot Table is like this

How does one perform a task like Table 2 in MS Excel? If possible a VBA script of it.

解决方案

If anyone interested in a VBA solution for the above problem:

Sub GetUniquesCount()
    Dim dict1 As Object, dict2 As Object
    Dim c1 As Variant, c2 As Variant
    Dim i As Long, lastRow As Long, count As Long

    Set dict1 = CreateObject("Scripting.Dictionary")
    Set dict2 = CreateObject("Scripting.Dictionary")

    lastRow = Cells(Rows.count, "A").End(xlUp).Row
    c1 = Range("A2:A" & lastRow)
    For i = 1 To UBound(c1, 1)
        dict1(c1(i, 1)) = 1
    Next i
    Range("D2").Resize(dict1.count) = Application.Transpose(dict1.keys)

    lastRow = Cells(Rows.count, "B").End(xlUp).Row
    c2 = Range("B2:B" & lastRow)
    For i = 1 To UBound(c2, 1)
        dict2(c2(i, 1)) = 1
    Next i

    For Each cel In Range("D2:D" & Cells(Rows.count, "D").End(xlUp).Row)
        count = 0
        For Each k2 In dict2.keys
            If k2 Like cel.Value & "*" Then
                count = count + 1
            End If
        Next k2
        cel.Offset(0, 1).Value = count
    Next cel
End Sub

这篇关于计算列中的唯一数据，然后将其与另一个唯一数据进行匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！