Shulou(Shulou.com)11/24 Report--

The filter in Excel must be often used by everyone. It can quickly filter out qualified records in a column.

And one of the advanced screening, we may not use a lot of it!

However, if we can make good use of it, we can greatly improve our work efficiency.

Today, I share with you the alternative use of advanced filtering.

❶ extracts the common items of the two tables.

❷ extracts different items from the two tables.

Let's take a look at it with me.

The common items of the two tables are extracted as shown in the following figure. In Table 1, we usually record a list of invoices:

Table 2, a table derived from the Internet:

Now I want to extract the data from both tables so that we can use it as our accounting and bookkeeping data for this month.

The specific steps for 👉 are as follows:

❶ first selects the cells in [A1] in the [Table 3] worksheet to store the filtered result data.

❷ clicks the Advanced filter button in the data tab to bring up the Advanced filter dialog box.

❸, click [list area] to select the data region in the [Table 1] worksheet, and then select [conditional region] to select the data range in the [Table 2] worksheet.

❹, click [copy filter results to another location], select [A1] cell in [Table 3], and then click "OK".

The result is as follows:

Finally, we filtered out the common data in both tables, and a total of 6 identical data were filtered out.

PS: when using advanced filtering, the title names in each table need to be the same!

If you put the filter results in a new table (for example, we put the filter results here in [Table 3]), you need to locate the active cell in [Table 3], and then call up the "Advanced filter" dialog box for subsequent operations!

In addition, here to tell you a little bit about the function approach, do next supplement.

We can also use the COUNTIFS count function to extract the same items from both tables.

As shown below:

Enter the following formula in the [F3] cell in [Table 1]:

= COUNTIFS, Table 2, Table 3, Table 2, Table 3, Table 2, Table 2, Table 3, form 3, Table 3, Table 3,

Count the number of occurrences of each cell in [Table 1] in [Table 2].

If the result is 1, the data in [Table 1] is in [Table 2].

If the result is 0, it is not in [Table 2].

Finally, the data with a filter result of 1 is the common item we want.

Does it look like the formula is very long? if there are more columns, the formula is still very troublesome to write.

And advanced screening can be done with the click of a mouse!

Extract the different items of the two tables, we filter out the same data of the two tables through advanced filtering, so how do we find out the different items of the two tables?

Some friends must have thought that the hidden data is the data that they do not have.

How to find them?

Let's take the above as an example, for example, we want to know which records in Table 1 are not in Table 2.

The steps for 👉 are as follows:

❶ select any cell in [Table 1], then click "Advanced" in [data], bring up the "Advanced filter" dialog box, and select the [A2:E12] data area.

❷ clicks the text box in the conditional area and selects the data area [A2:E12] in Table 2. Finally, click [OK].

❸ sets the font of the filtered data to blue (as long as it is different from the original font color).

❹ cancels [Advanced filter] and sets it to [filter].

PS: click the "filter" button directly, and you can automatically cancel the "Advanced filter" function.

❺ selects [filter by font color]-[automatic] in [filter by Color].

The final effect is as follows:

Among them, four records are different from the data in [Table 2]: either the invoice number is different, or some other cells are different.

Of course, we can also use the Countif function described above to filter out the row of the cell displayed as 0, which is the result we want.

By learning the advanced filtering methods above, we can easily solve the differences between the two tables.

However, if we use more than 15-digit ID card numbers or bank card numbers as screening conditions, the results are not what we expect.

As shown in the figure below, we want to filter on the ID card number in [E2].

We follow the steps above to add [list area] and [condition area], then click [copy filter results to another location] and enter [F1] in the [copy to] text box.

The results are as follows:

All the records have been screened out.

What's going on?

The reason lies in the number of digits of this number.

If the number of digits exceeds 15 digits, the number that follows by default is 0.

What should we do in this case?

In fact, the solution is also very simple, as long as the ID card number followed by an asterisk ("*"), the problem will be solved.

The purpose of adding * is to force numbers into text.

The asterisk ("*") here is a wildcard.

Of course, if there is a lot of data, you can also use the formula to add it in batches:

The formula is as follows:

= E2 & "*" connects an asterisk with a text connector (&). Because the asterisk is a character, it needs to be surrounded by double quotes.

Let's do it again and add all the fields: choose to copy to the [F4] cell to store the result data, and finally click [OK].

The results are as follows:

So the result is right!

At the end of the day, I introduced the alternative use of advanced filtering:

❶ extracts the common items of the two tables.

❷ extracts different items from the two tables.

❸ solution to errors in screening numbers with more than 15 digits.

Have you learned everything?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart, Editor: Xiaoyin, Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.