vba 对一列进行排序以匹配 excel 中的另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19670058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sort one column to match another in excel
提问by Simon
I have a spreadsheet and I need to match the two columns together. However "Dove code" is 3600 rows and "code 2" is 1100. They all have the same codes as you can see in the image but you can also see where it starts changing and I need to have the codes all line up so I can see the gaps. I have already arranged them all alphabetically and its the "code 2" that would need to match up to "Dove code
我有一个电子表格，我需要将两列匹配在一起。然而，“Dove 代码”是 3600 行，“代码 2”是 1100 行。它们都具有与您在图像中看到的相同的代码，但您也可以看到它开始变化的位置，我需要将代码全部对齐，所以我可以看到缝隙。我已经按字母顺序排列了它们，它的“代码 2”需要与“鸽子代码”匹配
回答by Timevdv
If the above solution would result in too much shunting and vba is not an option, there's another way. Copy the first column and use 'remove duplicates' on it. Now you have an index list, put numbers from 1 to x in the column on the right of it.
如果上述解决方案会导致过多的分流并且 vba 不是一种选择，那么还有另一种方法。复制第一列并在其上使用“删除重复项”。现在您有了一个索引列表，将 1 到 x 的数字放在它右侧的列中。
Insert a column between the two lists and right of the second one.
在两个列表之间和第二个列表的右侧插入一列。
Assuming that the index list is in F and the numbers in G, put this formula in the cell right of the first cell in the larger list: =VLOOKUP(A2,$F$2:$G$500,2,FALSE)
假设索引列表在 F 中，数字在 G 中，将此公式放在较大列表中第一个单元格右侧的单元格中： =VLOOKUP(A2,$F$2:$G$500,2,FALSE)
Adjust the range accordingly. Put the same formula in the cell right of the first cell in the shorter list, with of course C2 instead of A2. Copy both formules to the end of the list.
相应地调整范围。将相同的公式放在较短列表中第一个单元格右侧的单元格中，当然使用 C2 而不是 A2。将两个公式复制到列表的末尾。
Now both columns have an index on every row. You can match them using data sort, but for that you need to add dummies in the index columns.
现在两列的每一行都有一个索引。您可以使用数据排序来匹配它们，但为此您需要在索引列中添加虚拟对象。
Put this formula in the cell right of your basic index list: =countif(B:B,G2) And this one in the cell right of that: =countif(D:D,G2)
将此公式放在基本索引列表右侧的单元格中：=countif(B:B,G2) 将这个公式放在右侧的单元格中：=countif(D:D,G2)
Now you know how many times each record arises in both lists. Just add extra numbers manually so that both formulas turn up the same result. You should be able to do that really fast. If you have 200 records that are used 2 times in the first column and not in the second one, just copy the index of those 200 records and paste them twice. The countif's will automatically update. You can use an extra column to calculate the difference between the two counts and use data sort on your basic index list to sort on the diferences.
现在您知道每个记录在两个列表中出现的次数。只需手动添加额外的数字，这样两个公式就会得到相同的结果。你应该能够非常快地做到这一点。如果您有 200 条记录在第一列中使用了 2 次，而在第二列中没有使用，只需复制这 200 条记录的索引并粘贴两次即可。计数会自动更新。您可以使用额外的列来计算两个计数之间的差异，并在基本索引列表上使用数据排序来对差异进行排序。
After that just use data sort.
之后只需使用数据排序。
IF my directions are clear enough, this shouldn't cost you more than 10 minutes.
如果我的指示足够清楚，这应该不会花费您超过 10 分钟。
Edit: Here's an example: http://img14.imageshack.us/img14/6366/k8pg.jpg
编辑：这是一个例子：http: //img14.imageshack.us/img14/6366/k8pg.jpg
回答by pnuts
Without VBA I do this (for columns with a limited number of mismatches!) by adding a formula such as =INDIRECT("A"&ROW())<>INDIRECT("B"&ROW())in a helper column. Working downwards, every time you see a TRUE shunt the appropriate column down to suit. But it may be only just about viable for 1100 rows!
在没有 VBA 的情况下，我通过添加一个公式（例如=INDIRECT("A"&ROW())<>INDIRECT("B"&ROW())在辅助列中）来执行此操作（对于不匹配数量有限的列！）。向下工作，每次看到 TRUE 时，将适当的列向下分流以适应。但它可能仅适用于 1100 行！
