And soon there is accurate data verification on Excel!

In the normal work, we often need to check the data, but some of the data that need to be checked are in different columns, while others are in different worksheets. For these data, it can be quickly checked by the method of this paper.

Single-table data - Quick check of conditional formats

Many times the data that needs to be checked is in different ranks of a worksheet. For example, in the inventory statistics of a warehouse, the inventory number is automatically calculated according to the formula, and a real number is filled manually. Now we need to check the two columns of data in order to find out the data with different values ​​(Figure 1).

Figure 1 Checking inventory and firm counts

The simplest way to check this sort of data in different columns of the same worksheet is to use the conditional format for checking. Select the inventory number and firm number in turn, click on "Conditional Formats → Highlight Cell Rules → Duplicate Values" and set the duplicate value to the default light red display so that the values ​​in the two columns with different data can be visually identified. (figure 2).

Figure 2 Quickly Finding Data from Different Cells Using Conditional Formats

The conditional format can quickly filter out different data, but if there is a lot of data in each column, it is inconvenient to find it in a large number of data. For this type of data, you can also use the "conditional format + IF function" to check. First insert an auxiliary column F after the number of real disks, select F4, insert the formula "=IF(OR(D4=E4)," "correct", "check again"), and fill it down. The formula means to check the values ​​of columns D and E. If they are the same, they are displayed as “correct”; otherwise, “please check again”. Continue to select the F column, click on "Conditional Formatting → Highlight Cell Rule → Equals", and then in the popup window, set the format for the cell equal to the following value → enter "Please recheck" → set to light red fill with dark red text (image 3).

Figure 3 equals cell settings

This will display a red “Please check again” for cells with different values, and then sort the cells according to the color of Column F so that all the different data will be automatically arranged and highlighted (Figure 4).

Figure 4 Using IF Functions for Filtering and Sorting

Cross table check - filter + function efficient search

The same worksheet data can be quickly checked using the conditional format, but if the checked data is in different worksheets, other methods are needed for quick check. For example, in a warehouse inventory, many companies’ firm disks are attended by other staff, so that the actual disk number is entered in another worksheet, and because each warehouse item is different, the product inventory of each person is only part of the total library. . For example, there are 1024 products in the total inventory, and only 24 of them are in the inventory of Zhang San. Now it is necessary to quickly find items in inventory that differ from the number of stocks in the three-item inventory.

Obviously, the key to solving this problem is to find items with the same item names but different quantities in the two worksheets. This can be done with the VLOOKUP function. First locate D2 in the total library worksheet, and enter the formula "=VLOOKUP(A2,$A$2:$B$24,2,0)<>B2". The function of this formula is to find the name of the search material. The same, but different amounts of data (Figure 5).

Figure 5

Formula explanation:

Here is the use of VLOOKUP function to find the data, the formula A2 said that the search target, that is here to find the "material name", "Zhang three real table! $ A $ 2 : $ B $ 24" indicates the search range, that is, in the preparation of Zhang three Search in the disk report valid data area (A1-B24). "2" indicates the number of columns in the "return value" given in the second parameter. Since this is the search for "inventory quantity", it is located in the second column of the worksheet (that is, column B). Note here that the column The number is not the number of columns in the worksheet, but the number of columns in the search range. A "0" indicates an exact lookup (and a value of 1 or TRUE indicates a blur). <>B2 means that it is not equal to, that is, look up the data with different amount of inventory in the Zhang three real list.

Once you find the data that meets your criteria, then use advanced filters to check specific data. Click "Data → Filter → Advanced" in the "Advanced Filter" dialog box, select "Filter Results in the original area" for the filter method, and select all data areas in the "List of Inventory Tables" for the list area. The area is selected D1 → D2, Vlookup function to find the data (Figure 6)

Figure 6 filter settings

Click "OK", so that the total stock table will automatically display the three inventory items (other Zhang three no inventory or the number of items is automatically hidden), and its data is different from the number of inventory, according to the prompt to the data Fonts are marked in red, so that data on inventory and inventory inconsistencies can be quickly found in so many items in the Treasury and reviewed as required (Figure 7).

Figure 7 Filter can automatically list the required data

Continue to click on "Data → Filter → Clear". This will display all the original items of the library. You can see that the data marked in red is the data that meets the requirements. If there are other inventory tables, all the data matching the requirements will be marked with red in the same operation as above, and they will be sorted by using the cell colors (Figure 8).

Figure 8 shows all the required data after sorting

As you can see, similar to the same keywords in different worksheets, but with different values, in Excel we can use the VLOOKUP function as a conditional area, and then use an advanced filter to quickly find out the data. It is also worth noting here that the contents of the header lines of the two columns used for screening in the advanced screening must be consistent, as in this case, the search is for "material name" and "inventory quantity" (the contents of the two worksheets must be the same, Otherwise, the search will go wrong.) In addition, there must be no blank cells in the two columns used for the filter, such as the heading "Material Name". Usually we must develop a good habit of data input specification in our work, so that subsequent data sorting, searching and screening will not bring inconvenience.