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

The original title: "that's awesome!" Let VLOOKUP be powerless to merge tables, but these two tricks can be easily solved! "

Under the careful care of Xiao E, Akiba Excel is becoming more and more popular. In order to give back to fans, Xiao E made a "live surprise event", the product quota, full of surprise.

Activity rules, each order id, each product can only place one order.

However, when Xiao E delivered the goods, she found that many fans had placed orders for many different products.

At this time, he was thinking that if the goods were delivered separately and the pieces were simply counted, the logistics cost would be a little high. In line with the principle of saving money, he was thinking that he would try to merge the goods.

Therefore, Xiao E needs to combine the different products purchased by customers with the same name, the same mobile phone number and the same address to form a piece of order data for combined delivery in order to save costs.

After processing, as shown in the following figure, we can see that Xiao Shuang used different accounts to place orders many times for the Excel book:

PS: because the address is relatively long, we only merge the same name and the same mobile number in the case.

So the question is, what should we do with this? No, this is Xiao E's strong point.

Function method applicable version: Office2021,Office365,WPS2022

Since there are different accounts with the same buyer, we need to use the Countif function to count the total number of different products.

The question behind COUNTIFS ($Bath2VOULGRAPHY BESTRAN18ZHANGI2) turns into the problem of merging and summarizing product names based on name and cell phone number column.

Next, we merge the shipment quantity (merge similar items).

UNIQUE function to remove duplicates in last name column and mobile phone column.

This step is mainly to remove the duplicate values of the original last name column and the mobile phone column to facilitate subsequent filtering and merging.

FILTER function to filter the number of product names and totals based on last name and mobile phone column.

In this step, the number of product names is screened by name and mobile phone column.

= FILTER ($G$2:$G$15=L2) * ($H$2:$H$15=M2))

The basic syntax of the Filter function the Filter function is a filter function that can filter out the results in the array whose condition is True.

= FILTER (array or range to filter, filter condition, [ignore null])

The TEXTJOIN function, which separates the union data as specified.

In this step, multiple data are merged into one.

= TEXTJOIN (";", 1 H$2:$H$15=M2 filter ($G$2:$G$15=L2) * $H$2:$H$15=M2))

The basic syntax of the Textjoin function = Textjoin (delimiter, whether to ignore null values, array / cell range)

Enter TRUE if you want to ignore null values, and FALSE if you do not ignore null values.

Tips:

If you are Office2016 or above and do not have the Filter function, you can also use the if function instead, but the operation efficiency will be a little less. Because the formula is an array formula, you also need to press the array triple key [Ctrl+Shift+Enter] to end after the formula input is complete.

= TEXTJOIN (";", 1 J$2:$J$15 if (($G$2:$G$15=L2) * ($H$2:$H$15=M2), $Illum2)

If you don't want to use an auxiliary table, you can also use a formula to do it! However, this method is limited to Office365.

= TEXTJOIN (";", 1, LET (a B$2:$B$18, ($D$2:$D$18=G2) * ($E$2:$E$18=H2)), MAP (UNIQUE (a), LAMBDA (tJournal t & "*" & COUNTA (FILTER (a)

The main principles are as follows:

❶ filter specifies the product name (filter) corresponding to the name and mobile phone number, and the name is defined as a, which is convenient for further filtering count (let).

The product name (unique) after the ❷ loop (map) is removed, each product name is further screened and counted (filter,counta), and concatenated with "*".

❸ is finally separated by textjoin to coincide with the union.

The function method is flexible, but limited by version. So is there an easier way? The answer is yes, using plug-ins.

Square grid, almost all versions can be used.

Use it, no auxiliary tables, no functions, just a click of the mouse, you can meet the above requirements! Let's take a look.

Specific steps:

Select [Square Grid]-[find location]-[one-to-many search].

An one-to-many find dialog box appears.

Criteria: look up the name and mobile phone number column in the table

Conditional area: name and mobile phone number column in the data table

Data area: product name column in the data table

Result storage: the first cell of the shipment quantity in the query table

Match multiple results: do not expand, use, connect (repeat accumulation)

Click the [OK] button, and the result appears at once.

In the options, we can select the processing mode for multiple data found.

Expand to the right: that is, multiple data found are extended to multiple columns.

No expansion: you can choose how to connect and how to aggregate (number, sum, maximum, minimum, first, second, and last match).

Here we choose to use, connect (repeat accumulation).

The operation effect of dynamic graph is as follows:

So easy~

Finally, Excel plays a very important role in our lives. Data comes from business life, and Excel plays a more different role through this tool.

Through observation, Xiao E merges the order data, which saves part of the logistics cost for the boss. This paper mainly introduces two methods:

The new ❶ function Unique function removes duplicate data

Filter function to filter the qualified data.

The Textjoin function is merged.

Because we also need to summarize and merge multiple data, we also use an auxiliary table as a transition.

❷ square grid using plug-ins, no complex functions, just a click of the mouse can be done. Convenience is convenient, but because of its encapsulation, it must be far less flexible than the function.

Because of the limited space, the method is not limited to the article.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: 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.