在多个Excel工作簿中合并列并自动重复数据删除 [英] Combine Columns in multiple Excel workbooks and auto-de-dupe

问题描述

我在A列中有三个工作簿，这些工作簿具有ID.我要创建第四个工作簿，该工作簿应合并ID并自动对其进行重复数据删除，以便我可以对它们执行vlookup引用其他工作簿上的数据.其中包含数据的3个工作簿将不断更新，并添加新的ID号，因此我需要主/摘要工作簿来自动获取新添加的ID号并针对其他工作簿执行vlookup.

I have three workbooks with IDs in column A. I want to create a fourth workbook, which should combine the IDs and de-dupe them automatically so that I can perform a vlookup on them to reference data on the other workbooks. The 3 workbooks with data in them will be constantly updated with new ID numbers added, so I need the master/summary workbook to automatically grab newly added ID numbers and perform vlookups against the other workbooks.

这样做的目的是给出每个记录(对应于一个人)的摘要视图，让用户知道该人存在于哪些工作簿中.

The goal of this is to give a summary view of each record (which corresponds to a person), letting the user know which workbooks that person exists in.

我尝试做= max()检索每个工作簿中ID的数量，并将它们组合在一起，告诉我存在的ID总数，将它们组合起来.然后我尝试执行此操作:= SMALL(IF(FREQUENCY(Test1:Test2 $ A $ 2:$ A $ 1000，ROW($ 1:$ 28))<> 0，ROW($ 1:$ 28)，")，ROW( A1)) + CTRL + SHIFT + ENTER

I have tried doing =max() to retreive the number of ID's in each workbook, and combining them, telling me the total # of ID's that exist, combined. Then I tried to perform this: =SMALL(IF(FREQUENCY(Test1:Test2$A$2:$A$1000, ROW($1:$28))<>0, ROW($1:$28), ""), ROW(A1)) + CTRL + SHIFT + ENTER

但是我1.不确定是否可以使用，并且2.不确定该语法如何与3个单独的工作簿一起使用.

But I'm 1. not sure if that'll work and 2. not sure how the syntax works with 3 separate workbooks.

我也很幸运地在VBA中尝试了union方法-再次，我认为我搞砸了语法.

I also tried the union method in VBA with no luck - again I think I'm messing up the syntax.

推荐答案

您可以在母版中使用以下数组公式在[ID_First.xlsx] Sheet1！$ A $ 2:$ A $ 999中检索ID编号的唯一列表.工作表的A2(需要在其上方放置一行以避免循环引用).

You can retrieve a unique list of the id numbers in [ID_First.xlsx]Sheet1!$A$2:$A$999 using the following array formula in the master worksheet's A2 (needs a row above it to avoid circular references).

=IFERROR(INDEX([ID_First.xlsx]Sheet1!$A$2:$A$999,MATCH(0, IF(LEN([ID_First.xlsx]Sheet1!$A$2:$A$999),COUNTIF(A$1:A1,[ID_First.xlsx]Sheet1!$A$2:$A$999),1),0)),"")

如果连续堆叠相似的公式，并使用IFERROR()将计算传递给它们，则可以从三个单独的工作簿中获得唯一列表.

If you stack similar formulas consecutively, passing calculation on to them with IFERROR(), you can gain a unique list from three separate workbooks.

=IF(LEN(A1),IFERROR(INDEX([ID_First.xlsx]Sheet1!$A$2:$A$999,MATCH(0, IF(LEN([ID_First.xlsx]Sheet1!$A$2:$A$999),COUNTIF(A$1:A1,[ID_First.xlsx]Sheet1!$A$2:$A$999),1),0)),IFERROR(INDEX([ID_Second.xlsx]Sheet1!$A$2:$A$999,MATCH(0, IF(LEN([ID_Second.xlsx]Sheet1!$A$2:$A$999),COUNTIF(A$1:A1,[ID_Second.xlsx]Sheet1!$A$2:$A$999),1),0)),IFERROR(INDEX([ID_Third.xlsx]Sheet1!$A$2:$A$999,MATCH(0, IF(LEN([ID_Third.xlsx]Sheet1!$A$2:$A$999),COUNTIF(A$1:A1,[ID_Third.xlsx]Sheet1!$A$2:$A$999),1),0)),""))),"")

数组公式需要按Ctrl + Shift + Enter才能完成.输入正确后，请根据需要填写以收集所有唯一ID.

Array formulas require Ctrl+Shift+Enter to finalize. Once entered correctly, fill down as necessary to collect all unique IDs.

有了唯一的ID号列表，您可以使用嵌套IFERROR函数的相同方法在三个工作簿系列中查找其他数据.

With a unique list of id numbers, you can use the same method of nested IFERROR functions to look through a series of three workbooks for additional data.

=IFERROR(VLOOKUP($A2, [ID_First.xlsx]Sheet1!$A$2:$Z$999, 2, FALSE),IFERROR(VLOOKUP($A2, [ID_Second.xlsx]Sheet1!$A$2:$Z$999, 2, FALSE),IFERROR(VLOOKUP($A2, [ID_Third.xlsx]Sheet1!$A$2:$Z$999, 2, FALSE),"")))

我正在提供此服务，因为您提到了总共50个会员ID.当应用于较大的数字组时，该方法可以快速(对数地)消耗计算资源.

I'm offering this as you've mentioned a total of 50 member IDs. This method can quickly (and logrythmically) eat up calculation resources when applied to larger groups of numbers.

这篇关于在多个Excel工作簿中合并列并自动重复数据删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！