Several techniques for data alignment and lookup in Excel tables

Source: Internet
Author: User
Tags header

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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.