Excel looks for comparison questions

1. Select A1 first, and then use Format Conditional Format in the menu, when its value is equal to A1, set the "Format" to red; In the same way, do the b1 conditional grid, then select these two cells, click the format brush, brush the possessive box below, because the data is too long, so you can divide the window into two departments first, and the lower department is located to the end with ctrl end.

2. Sort the two tables first, then add a row of data with the same word in the second column of Table 2, such as "Yes", and enter =vlookup(a1,sheet2!) in b1 of Table 1a:b,2,0), when the result is "yes", otherwise it is n a, drop down the formula, you use conditional formatting, when the value is not"There is a "time highlight.

good luck!!!

Hope it helps.

Suppose that the data in both tables starts from row 2 to row 100.

Enter the formula: in column D of Sheet1

d2=if(a2=sheet2!a2,if(c2<>sheet2!c2,c2,""),"")

Copy down to line 100 after entering.

Enter in Table 1, d1.

vlookup (A1, Table 2!.)a:c,3,false)=c1 to copy the formula downwards.

false: The data is different.

1. The following example demonstrates the operation method. For example, in the figure below, the two columns of data are infiltrated to see if the two columns of data are the same.

2. Enter the mouse in cell C2: =A2-B2; This means that the cluster data of cell A2 minus the data of cell B2. If the number is 0, then the two data are the same; Otherwise it's different.

3. Press the Enter key and get 0; Then the data for A2 and B2 are the same. Place the mouse in the lower right corner of cell C2.

4. Click and hold the scroll down to the C12 cell. Mu Zhao let go of the mouse and saw all the numbers instantly.

5. Then, the data in rows 5, 7, and 10 are different from these numbers.

Excel checks the data, it is much easier to use small tricks, and you don't need to spend a lot of time looking at the differences in the data one by one. The following is to share with you the Excel data comparison to find out the different and the same data.

1、ctrl+

Select the data directly, press Ctrl+, and quickly find different data.

2、ctrl+g

Select the data and press the shortcut key.

Ctrl+G, click the targeting condition, and select [Row Content Difference Cell] to quickly mark the cells with different data.

3. Countif function.

Enter the function formula =countif(b2:b10,c2), and the result is 0, which means different data.

4. If function.

Enter the formula =if(b2=c2,"Same","Not the same"When the results show that they are not the same, that's what we're looking for.

5. Exact function.

Enter the formula =exact(b2,c2), and the result is displayed as false, which is a different data.

The above is Excel comparing data to find different data.

Judging from the data in the figure, it is estimated that the data is b215 with a space after it, such as"Zhang San"with"Zhang San", the latter is with a space, which is the same on the surface of excel, and can only be seen by double-clicking the cell to enter the state.

Solution 1:

Select column b -->ctrl+f-->replace --> search content, enter a space --> replace all--> close. (Column C can also be executed this once).

Solution 2: Change the formula in the figure to this:

if(b215="","",if(text(trim(b215),0)=left(trim(c215),12),"The material is consistent","Material error"))

Check whether the data in column C has invisible characters or spaces.

You check if your data itself is wrong, you can use trim() to clean up the data you want to make a judgment and then backfill it!! The formula is no problem!