Office hacks: compare two columns of names 3 methods you can choose

In the daily office, comparing the similarities and differences of two columns of names is often encountered, the dumb method is to respectively list the two columns of names in ascending or descending order, and then one by one to compare, this method is time-consuming and laborious. In fact, you can also use the function formula, advanced filtering, conditional formatting and other methods to easily achieve the comparison of the two columns of names (Figure 1).

2013A-XMDB-1

1. function formula method

Through COUNTIF function and general screening to achieve. In cell B2, enter the function formula “= COUNTIF (C: C, A2)”, down to fill the corresponding cell; the same, in cell D2, enter the function formula “= COUNTIF (A: A, C2)”, down to fill the the corresponding cell. selected cell B1 (that is, “auxiliary column 1”), click “Sort and Filter → Filter”, click the drop-down arrow at cell B1, only check the “1”, OK selected after the screening A data cell, set the fill color, click the drop-down arrow at cell B1 again, check “Select All”; then click the drop-down arrow at cell D1, check only “1”, OK, then select the filtered C data cell, set the fill color, click cell D1 again, click the drop-down arrow at cell D1, check only “1”, OK, then select the filtered C data cell, set the fill color, click the drop-down arrow at cell D1 again. fill color, click the drop-down arrow at cell D1 again, check the “Select All”. In this way, column A and C in the same name of the cell is identified (Figure 2).

2013A-XMDB-2

Tip: This method is applicable to the comparison of data tables without a fixed number of data tables in the corresponding column can be added or deleted at will.

2. Advanced filtering method

As long as the two columns in the advanced screening of each other as the conditions of the other column area for screening, the results of the screening color can be filled. Selected A data area, switch to the “Data” tab, click “Sort and Filter → Advanced” in the pop-up window, select “in the original area to display the results of the filter”. “Conditional Area” at the selection of the C data, the filtered cells in column A to fill the color; next, select any data cells in column A, in the “Data” tab, click “Clear “; select the C data area, click “Sort and Filter → Advanced”, in the pop-up window, select “in the original area to display the filter results”, “condition area “at the selection of the data in column A, will be filtered in column C cells for color filling; Finally, select any data cells in column C, in the “Data” tab click “Clear”, column A and C in the name of the same cell is marked out (Figure 3).

2013A-XMDB-3

Tip: This method is suitable for the function is not familiar with the office of the white, also applies to a fixed number of data table comparison. 3.

3. conditional formatting method

Conditional formatting method is very simple, just hold down the Ctrl key to select the data area of column A and C, in the “Start” tab, click “Conditional formatting → highlight the cell display rules → repeat value”, in the pop-up window custom fill color in the pop-up window. In this way, column A and C of the same name in the cell is identified (Figure 4).

2013A-XMDB-4

Tip: This method is suitable for relatively small and fixed data tables, if more data or the need to delete data at any time, you need to choose another way.

Leave a Comment