在两个列表中查找数据并相互比较

this is my first time posting here, but I didn't know where else to go.

这是我第一次在这里发帖，但我不知道还能去哪里。

I have two lists in excel, both of them with similar data but in different orders, each one has 3 columns (prefix (A, D), suffix (B, E) and data (C, F)).

我在excel中有两个列表，它们都有相似的数据，但顺序不同，每个列有3列（前缀（A，D），后缀（B，E）和数据（C，F））。

Let's say list 1 is in columns A, B and C; and list 2 in D, E, F.

假设列表1在列A，B和C中;并列出D，E，F中的2。

Each element in column C has an equal one in column F, but in different order and also, the previous two columns (prefix and suffix) may or may not have different values from their counterpart.

列C中的每个元素在列F中具有相等的一个，但是以不同的顺序并且前两个列（前缀和后缀）可以具有或不具有与它们的对应物不同的值。

I want to do this (I guess it has to be done with macros but I don't really know, I'm not too much of an expert with excel), I want to take each element from column C (data) and find it's equivalent in column F, after that, I want to compare their prefix and suffix, and maybe paint it red when they're different and green when they're the same.

我想这样做（我想它必须用宏完成，但我真的不知道，我不是太多的excel专家），我想从列C（数据）中取出每个元素并查找它在F列中是等价的，之后，我想比较它们的前缀和后缀，并且可能在它们不同时将其绘制为红色，在它们相同时将其绘制为绿色。

Is this possible? (and not too much of a hassle?)

这可能吗？ （并没有太多的麻烦？）

If you didn't understand something, please reply and I'll be glad to try and explain better.

如果你不明白的话，请回复，我很乐意尝试更好地解释。

2 个解决方案

#1

I'd do the following:

我会做以下事情：

1. Move column F before columns D and E (now D is the data, E is the prefix and F is the suffix)

   在列D和E之前移动列F（现在D是数据，E是前缀，F是后缀）

2. Create three new columns G, H and I with the formulas:

   使用公式创建三个新列G，H和I：

   =vlookup(C1, D:F, 2, 0)

   = vlookup（C1，D：F，2,0）

   =vlookup(C1, D:F, 3, 0)

   = vlookup（C1，D：F，3,0）

   =and(G1=A1,H1=B1)

   =和（G1 = A1，H1 = B1）

3. Place conditional formatting on column I, with red for FALSE and green for TRUE.

   在第I列放置条件格式，红色表示FALSE，绿色表示TRUE。

The first step is necessary because VLOOKUP will look for the value in the first column of data.

第一步是必要的，因为VLOOKUP将在第一列数据中查找值。

On the second step, you will match the column C with column F (now moved to D) and obtain the corresponding prefix and suffixes. The next formula compares both and returns TRUE or FALSE if they both match.

在第二步，您将匹配列C与列F（现在移动到D）并获得相应的前缀和后缀。下一个公式比较两者，如果它们都匹配则返回TRUE或FALSE。

You could combine all three formulas in one, if needed.

如果需要，您可以将所有三个公式合并为一个。

#2

You can do it without macros by using conditional formatting

您可以使用条件格式在没有宏的情况下执行此操作

(There is an excellent write up of conditional formatting at Debra Dalgleish's site here. In xl2003 conditional formatting is accessed via the Format .. Conditional Formatting menu.)

（在Debra Dalgleish的网站上有一个很好的条件格式编写。在xl2003中，可以通过Format .. Conditional Formatting菜单访问条件格式。）

If your data to be matched was in A1:F10 then these two formulas can be used to

如果您要匹配的数据是A1：F10，则可以使用这两个公式

• match column C against F and return the position in F where column C has a match
• 将列C与F匹配，并返回F中的位置，其中列C具有匹配项
• compare the values in column D and E in this position to the respective values in columns A and B
• 将此位置的D列和E列中的值与A列和B列中的相应值进行比较
• format column A and B for valid matches using =A1=INDEX(D$1:D$10,MATCH($C1,$F$1:$F$10,0))
• 使用= A1 = INDEX格式列A和B表示有效匹配（D $ 1：D $ 10，MATCH（$ C1，$ F $ 1：$ F $ 10,0））
• format column A and B for invalid matches using =A1<>INDEX(D$1:D$10,MATCH($C1,$F$1:$F$10,0))
• 格式列A和B表示无效匹配使用= A1 <> INDEX（D $ 1：D $ 10，MATCH（$ C1，$ F $ 1：$ F $ 10,0））

In the sample below I have added these two conditional formats to cells A1:B10 in xl2010

在下面的示例中，我将这两个条件格式添加到xl2010中的单元格A1：B10

A valid match is green Invalid matches are red Non matches are not formatted

有效匹配为绿色无效匹配为红色非匹配未格式化

So A1 and B1 are green as they match D2 (Mr) and E2 (Jones) for a common value of 1 in C1 and F2
So A3 is green as it matches D1 (Father) for 10 in C3 and F1, but B3 is red as E1 does not contain "wang"

所以A1和B1是绿色的，因为它们匹配D2（Mr）和E2（琼斯），因为C1和F2的共同值为1所以A3是绿色的，因为它在C3和F1中匹配D1（父亲）10，但是B3是红色的因为E1不包含“wang”