基于唯一ID将两个大表合并成一个表 [英] Combine two large tables into one table based on unique ID

问题描述

要开始，我几乎没有VBA的知识，也没有尝试编写代码，因为我甚至不知道从哪里开始。

<我目前有两个表。表1包含48000行数据和两列，每个ID的唯一标识符和现金金额。表2包含50000行数据和两列，每个ID的唯一标识符和现金金额。 ID号码对于自己的表是唯一的，所以在另一个表中经常有重复的ID。目的是将两个表格按ID号组合，并显示每个ID号码的总现金金额。

我第一次尝试使用SUMIF函数来获取我两张桌子的总计。虽然这适用于第一个ID，但当我尝试将公式复制到其他单元格时，笔记本电脑完全崩溃，强制重新启动。

我的第二次尝试涉及使用数据透视表向导组合两个范围。但是，我发现数据透视表无法处理这么多唯一的值。 （基于出现的弹出窗口）。

我第三次尝试工作，但我发现很长时间，我希望有一个更好的方法。我将我的表分成两个大约20,000行的范围（现在有4个表）。然后我使用数据透视表向导来一次组合这两个。首先是Table1和Table3，然后是Table2和Table4。然后，我不得不再次分割结果列表，因为数据透视表无法处理它，并重复此过程。这种方法的问题是我觉得由于所有的分裂，都有可能错过或重复的值。

在这三次尝试中，我的电脑重复问题和需要重新启动。

只要有效，我不在乎VBA解决方案需要一段时间才能运行。

我已经尝试了其他的例子，但有一些我不知道如何应用于我的情况，而其他人似乎没有使用足够大的文件来体验我面临的一些问题

谢谢，如果您需要澄清任何事情，请让我知道。

解决方案

最后，我使用数据透视表向导组合10,000个批量的范围。

感谢您的帮助。

To start, I have little knowledge of VBA and have not tried to write a code for what I want to do as I don't even know where to start.

I currently have two tables. Table1 contains 48000 rows of data and two columns, a unique identifier and a cash amount for each ID. Table2 contains 50000 rows of data and two columns, a unique identifier and a cash amount for each ID. ID numbers are unique to their own table so there are often repeated IDs in the other Table. The goal of this is to combine the two tables by ID number and show the total cash amount for each ID number.

My first attempt involved using the SUMIF function to get me the totals from both tables. Although this worked for the first ID, when I tried to copy the formula to the other cells, my laptop crashed completely forcing a restart.

My second attempt involved using PivotTable wizard to combine the two ranges. However, I discovered that PivotTables cannot handle this many unique values. (Based on the popup that appeared).

My third attempt worked but I found it long and I am hoping there is a better method. I split my tables into two ranges of about 20,000 rows (so now there are 4 tables). I then used the PivotTable wizard to combine these two at a time. First was Table1 and Table3, then Table2 and Table4. Then I had to split the resulting lists again as PivotTable couldn't handle it and repeated this process. The issue with this method is I feel there is a definite possibility of missed or repeated values because of all the splitting.

During all three of these attempts, my computer repeatedly had problems and required restarts.

I don't care if a VBA solution takes a while to run, as long as it works.

I have tried looking at other examples but some I couldn't figure out how to apply them to my situation and others seemed like they weren't working with big enough files to experience some of the problems I am facing.

Thank you and please let me know if you need clarification on anything.

解决方案

In the end, I used PivotTable Wizard to combine the ranges in batches of 10,000.

Thank you for your help.

这篇关于基于唯一ID将两个大表合并成一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！