Excel2011:Vlookup和合并 [英] Excel2011: Vlookup and Combine

问题描述

我在将多个功能组合到70000+行的excel文件中时遇到一些困难.任何提示，指示或建议都将不胜感激.

I'm having some difficulty combining several functions to do what I want in a 70000+ line excel file. ANY tips or pointers or advice greatly appreciated.

我有2列(价值约70000行).在第1列中，我有客户的帐号(重复)，在第2列中，我旁边有要提取的数据.我还有第三列(第3列)，它是帐号的列表，但已删除了重复项.我正在尝试让我们Vlookup查看第三列的第一行(lookup_value)，然后在(table_array)的第一列中搜索该值，并从第二列返回与第一列值相邻的值.

I have 2 columns (about 70000 lines worth). In column 1 I have account numbers for clients (there are duplicates), next to it in column 2 I have the data I want to extract. I also have a third column (column 3) which is a list of the account numbers but has been stripped of duplicates. I am trying to us Vlookup to look at line one of Column three (lookup_value) then search for that value in columns 1 within (table_array), and return the value from column 2 that is adjacent to the column 1 value.

问题，我希望Vlookup对所有70000行执行此功能，以便它返回与(lookup_value)为其提供的特定帐号匹配的所有数据.然后，我想使用Combine函数，使用此Combine函数将数据字符串放入单个单元格中:

Problem, I want Vlookup to perform this function for all 70000 rows, such that, it returns all the data that matches that particular account number provided to it with (lookup_value). THEN, I want to use the Combine function to put the string of data into a single cell using this Combine function:

Function Combine(WorkRng As Range, Optional Sign As String = ", ") As String

    'Update 20130815
    Dim Rng As Range
    Dim OutStr As String
    For Each Rng In WorkRng
        If Rng.Text <> ", " Then
            OutStr = OutStr & Rng.Text & Sign
        End If
    Next
    Combine = Left(OutStr, Len(OutStr) - 1)

End Function

最后，我希望在第3列旁边，在每个帐号旁边的单个单元格中用逗号分隔数据.以下是我要执行的操作的示例.我有前三列，但我想将其转换为第四列.

Ultimately, next to column 3, I'd like the data to be separated by commas in a single cell, next to each account number. Below is an example of what I'm trying to do. I have the first 3 columns, but I want to convert it into the 4th column.

Acct #  Data        Accounts    Desired Data formating
1001    80100       1001        80100, 80250, 80255
1001    80250       1005        81000, 81222, 81235, 85213
1001    80255       1099        82250, 82323, 80100, 80150
1005    81000           
1005    81222           
1005    81235           
1005    85213           
1099    82250           
1099    82323           
1099    80100           
1099    80105           

我认为这将是一个简单的函数或公式，但也许我没有使用正确的函数或公式.

I thought this would be a simple function or formula, but maybe I'm not using the right one(s).

推荐答案

CSE作为数组公式输入时，tag"> textjoin 函数可以接受条件.

=TEXTJOIN(", ", TRUE, IF(A2:INDEX(A:A,MATCH(1E+99,A:A))=C2, B2:INDEX(B:B,MATCH(1E+99,A:A)), TEXT(,)))

如果您没有较新的 textjoin 函数在您的Excel版本中进行搜索，请搜索此站点的textjoin VBA UDF标记和工作表公式交替显示.我已经使用static dict as scripting.dictionary创建了一对.

If you do not have the newer textjoin function in your version of Excel, search this site's textjoin tag for VBA UDF and worksheet formula alternates. I've created a couple using static dict as scripting.dictionary.

这是一些标准的公共模块代码，它们将使用2-D数组和脚本字典将它们全部收集起来.

Here is some standard public module code that will collect them all using a 2-D array and a scripting dictionary.

_{此子过程要求，您需要使用工具，参考"将Microsoft脚本运行时添加到VBA项目中.}

_{This sub procedure requires that you add Microsoft Scripting Runtime to the VBA project using Tools, References.}

Option Explicit

Sub qwewrety()
    Dim delim As String, arr As Variant
    Dim d As Long, dict As New Scripting.dictionary

    delim = Chr(44) & Chr(32)

    With Worksheets("sheet3")
        arr = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value2

        For d = LBound(arr, 1) To UBound(arr, 1)
            If dict.exists(arr(d, 1)) Then
                dict.Item(arr(d, 1)) = dict.Item(arr(d, 1)) & delim & arr(d, 2)
            Else
                dict.Item(arr(d, 1)) = arr(d, 2)
            End If
        Next d

        .Cells(2, "C").Resize(dict.Count) = Application.Transpose(dict.keys)
        .Cells(2, "D").Resize(dict.Count) = Application.Transpose(dict.items)

    End With
End Sub

这篇关于Excel2011:Vlookup和合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！