Several techniques for data alignment and lookup in Excel tables

Several techniques for data alignment and lookup in Excel tables

Application Case One: Take out the intersection of two tables (the same part)

Sheet1 contains a list of A,sheet2 that contains a list B, to get two copies of the list of data records (intersections), that is, to find the same part of two lists.

Method 1: Advanced filtering

Advanced filtering is a powerful tool for processing duplicate data.

Select the data range that contains the first list of data, and then click Data--"Advanced" (2003 version of the menu action is "data"--"filter"--"Advanced Filter"), and the Advanced Filter dialog box appears.

In the dialog box, filter mode can be selected based on requirements, such as selecting "Copy filter results to another location", "List area" is the range of cells in which you selected the first list of data before, and criteria range to select the range of cells that contains the other list B. As shown in the following illustration:

Click on the "OK" button, you can directly get the intersection of two lists, the effect of the following figure. Of these, there were "watermelons" and "pineapples" in two of the lists, but they were not extracted as the same records because of the inconsistent numbers.

The rationale for this operation is to use the advanced filtering function to filter the records that match the specified criteria, to have any one of the two tables as a condition area, to filter out the matching records in another table, and to ignore other unrelated records.

It's important to note that when you use advanced filtering, be sure to note that the header row for the two listings is consistent (the premise of the conditional area in the Advanced filter), and that the scope of the header row is included when you select list area and conditional range.

Method 2: Formula method

There are many ways to use formulas for comparison, if it is a single row of data compare the commonly used function is the COUNTIF function, if it is more than one column of data record contrast, sumproduct function is more competent.

Enter a formula next to one of the listings:

=sumproduct ((a2&b2=sheet2! a$2:a$13&sheet2! b$2:b$13) *1)

and copy the fill down. One of the sheet2! A$1:a$13 and sheet2! B$2:b$13 is a two-column data area in another list that needs to be modified according to the actual situation. A record with a formula result equal to 1 is the intersection of two listings, as shown in the following illustration:

Application case Two: Take out the difference record of both tables

To remove a difference from another table in one sheet, which is not in the other part of the list, the principle and operation are similar to the first scenario above, except that the selected set of filters is just complementary.

Method 1: Advanced filtering

First change the header row of the two listings to keep it consistent, and then select the data range that contains the first list of data, and then click Data--Advanced in the ribbon--and the Advanced Filter dialog box appears. In the dialog box, select Show filter results in an existing area, and the selection of list area and criteria range are exactly the same as the previous scene 1, as shown in the following illustration: