How to determine whether two columns of data correspond one to one in Excel

Method: 1. Enter the formula "IF(A2=B2,"Same","Different")" in cell C2, and then press the Enter key. 2. Select two columns of data and use the shortcut key "Ctrl G"; select "Positioning Conditions" - "Row Content Difference Cells" in the pop-up dialog box, and click "OK".

The operating environment of this tutorial: Windows 7 system, Microsoft Office Excel 2010 version, Dell G3 computer.

At work, we often need to compare whether two columns of data or text are the same. If it is relatively simple and relatively small, we can check one by one with the naked eye, or if they are all numbers, we can use subtraction. To check, the ones equal to 0 are definitely the same, but for more complex and large content, naked eye recognition is obviously not possible.

Compare whether the contents of cells An and Bn are the same

For example, compare whether the data in columns one and two in the figure below are the same. Note that the comparison here is the same row. Data in different columns, that is, whether A2 and B2 are the same, whether A3 and B3 are the same... and so on, regardless of whether the contents of other cells in columns A2 and B except B2 are the same.

Method 1: A2=B2

Operation method: Enter the formula in cell C2: =IF(A2=B2,"Same"," Different"), then press the Enter key to complete. The translation of the IF function formula is that if A2=B2, the output result is "the same", otherwise it is "different".

It should be noted here that quotation marks are required for both similarity and difference, and they must be quotation marks under the English input method, otherwise the formula will be invalid.

The specific operation steps are as follows:

Method 2: Positioning conditions (Ctrl G)

Operation method: Select two columns of data—Use Shortcut key Ctrl G—select positioning conditions in the pop-up dialog box—select cells with differences in row content—click OK. EXCE will automatically select different cells, and you can fill these cells with color for easy viewing.

The specific operation steps are as follows:

Method three: Ctrl

Operation method: Select two columns of data—use the shortcut key Ctrl, EXCE Different cells will be automatically selected, and these cells can be filled with color for easy viewing.

The specific operation steps are as follows:

It should be noted that the above three methods have no problem when comparing text and numerical values, but there is a The problem is that they do not distinguish between upper and lower case letters when comparing. Therefore, if the two columns of data being compared contain English letters, deviations will occur using the above three methods.

For example, the following situation:

The method introduced below can avoid this situation.

Method 4: EXACT

Operation method: Enter the formula in cell C2: =EXACT(A2,B2), and then press the Enter key to complete. If the output result is TRUE, then Same, FALSE means different, and then filter out FALSE to find the different ones.

The specific steps are as follows:

Related learning recommendations: excel tutorial

The above is the detailed content of How to determine whether two columns of data correspond one to one in Excel. For more information, please follow other related articles on the PHP Chinese website!