将列数据映射到匹配行 [英] Map column data to matching rows

问题描述

我有一张这样的表：

| A | B | C | D | E | F | G | H | ...
---------------------------------
| a | 1 |   | b | 2 |   | c | 7 |
---------------------------------
| b | 2 |   | c | 8 |   | b | 4 |
---------------------------------
| c |289|   | a | 3 |   | a |118|
---------------------------------
| d | 6 |   | e | 3 |   | e |888|
---------------------------------
| e | 8 |   | d |111|   | d |553|
---------------------------------

我希望工作表如下所示：

I want the sheet to become like this:

| A | B | C | D | E | F | G | H | ...
---------------------------------
| a | 1 | 3 |118|   |   |   |   |
---------------------------------
| b | 2 | 2 | 4 |   |   |   |   |
---------------------------------
| c |289| 8 | 7 |   |   |   |   |
---------------------------------
| d | 6 |111|553|   |   |   |   |
---------------------------------
| e | 8 | 3 |888|   |   |   |   |
---------------------------------

Col A，Col B和Col G都有唯一的字母，而在col旁边它有重量。

Col A, Col B and Col G have letters which are unique, and in the col next to it it has weights.

为了使之更清楚，

| A | B |
---------
| a | 1 |
---------
| b | 2 |
---------
| c |289|
...

是a，b，c ...的权重， strong> 1月

are the weights of a,b,c... in January

同样 | D | E | 是七月和中的a，b，c ...的权重G | H | 是十二月中的a，b，c ...的权重

我需要把它们放在一边为了比较，事情是他们不按顺序。

I need to put them side-by-side for comparison, the thing is they are NOT in order.

我该如何处理？

更新

有数千个a，b，c，aa，bb，cc，aaa，avb，as，saf，sfa等等。其中一些可能在1月份（Col A）而不是7月份（Col D）。

There are thousands of a,b,c, aa, bb, cc, aaa, avb, as, saf, sfa etc.. and some of them MAY be present in January (Col A) and not in July (Col D)

推荐答案

像这样

代码

Sub Squeeze()
[c1:c5] = Application.Index([E1:E5], Evaluate("IF(A1:A5<>"""",MATCH(A1:A5,D1:D5,0),A1:A5)"), 1)
[d1:d5] = Application.Index([H1:h5], Evaluate("IF(A1:A5<>"""",MATCH(A1:A5,G1:G5,0),A1:A5)"), 1)
[e1:h5].ClearContents
End Sub

第一行说明

 Application.Index([E1:E5], Evaluate("IF(A1:A5<>"""",MATCH(A1:A5,D1:D5,0),A1:A5)"), 1)

• MATCH 返回与 A1：A5 相对于 D1：D5


• INDEX 然后从 E1中返回相应的值：E5


• The MATCH returns a VBA array matching the positions (5) of A1:A5 against D1:D5
• INDEX then returns the corresponding values from E1:E5

所以要使用A1：A100的键列对M1：100，值为N1： 100

So to use the key column of A1:A100 against M1:100 with values in N1:100

Application.Index([N1:N100], Evaluate("IF(A1:A100<>"""",MATCH(A1:A100,M1:M100,0),A1:A100)"), 1)

这篇关于将列数据映射到匹配行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！