Comparison of Two Excel tables

Comparison of Two Excel tables
My colleague asked me today that there are two Excel tables in the same format, but one of them has more entries than the other. Now I want to find out the items that are different from each other and ask me what I can do. I thought about it. It seems that I have no choice but to manually check it. I have more than one thousand records, which is quite tiring.

Later, I thought it was unreasonable to have such a problem. There should be a way to quickly search for it. I have never used it before. Check it online. The query is actually implemented by using the built-in functions in Excel. The formula provided on the Internet is as follows:

= If (iserror (MATCH (B, sheet1! B: B, 0), "false", indirect ("sheet1! D "& match (B, sheet1! B: B, 0 )))

I didn't understand it after reading it for a long time. He said that he was looking at the help of Excel. Let's also look at the help.

It turns out to be some functions. The format of the IF function is if (logical_test, [value_if_true], [value_if_false]), which is equivalent to the IF statement and is operated based on the logical value.

The iserror function is a judgment function in the format of iserror (value). When the value is an arbitrary error value (# N/A, # value! , # Ref! , # DIV/0! , # Num! , # NAME? Or # null !) The return value is true. Otherwise, the return value is false.

The match function is used to search for specific values. The format is match (lookup_value, lookup_array, match_type), that is, to search for the value of "lookup_value" within the range specified by "lookup_array, match_type is the search method, which can be-1, 0 or 1. If the value is 0, it indicates that the value is equal to "lookup_value. If yes, the position of the target value is returned (not the value itself). If no value is found, the error value # N/A is returned.

The indirect function references cells in the format of indirect (ref_text, A1). ref_text indicates the location of the cells to be referenced. For example, A1; A1 indicates a logical value and indicates the reference type.

 

 

 

This is hard to understand. Here are some small examples:

① Match Function

A B

1 product count

2 bananas 25

3 oranges 38

4 apples 40

5 pears 41

 

= Match (41, B2: B5, 0) the returned value is the position of 41 in column B: 4 (note not B4)

= Match (39, B2: B5, 0) # N/A is returned because no matching value exists.

 

② Iserror Function

Iserror (4) = false

Iserror (# N/A) = true

 

③ Indirect Function

A B

1. Data

2 B2 1,333

3 B3 45

4 George 10

5 5 62

 

= Indirect ($ A $2) reference value (B2 value) 1,333 in cell A2

= Indirect ($ a $4) If cell B4 has a defined name "George", return the value of B4 10.

= Indirect ("B" & $ a $5) The value of cell A5 is assigned to the function, that is, the return value is the value 62 of cell B5.

 

 

 

Well, let's look back at the formula given. It is generally an if statement that makes a judgment on the condition. If it is true, It outputs "false". If it is false, the content of the referenced cell is output. This statement is used to compare the two sheets and find out the differences. If the same content is found in another table, the returned value is a numerical value. Then, the iserror function returns the false value. The if statement is used to execute the indirect function and fill in the relevant cell content; if the same content is not found in another table, the returned value is # N/A. In this case, the iserror function returns true, and the IF statement outputs "false ".

The original purpose of the author is to find the similarities between the two tables, and then paste the content of the same part into another table. As long as my colleagues find the differences, I will be lazy, take only one part, that is:

= Iserror (MATCH (B, sheet1! B: B, 0 ))

In this way, only the values of true and false are returned, and then sorted to find the different items ^_^.

Pay attention to the parameters in the function. "B" indicates the position of the cell. If you enter this function in cell B1, this is B1; "sheet1" is the name of another sheet in the same Excel table. For different Excel tables, the format is [Table] sheet1; B: B indicates the entire B column. Of course, the most convenient way is to use the mouse to automatically generate these formats.

The final formula I used today is as follows:

= Iserror (MATCH (C1, data 1513! H: H, 0 ))