Office hacks: splitting and merging without fear Quantitative duplicate data so split and merged

In daily work, it is often necessary to convert quantitative figures in data input format. For example, column A in the table shown is the data reported by each salesperson on the promotion day of a mall, because the data is reported in the WeChat group using cell phone records, so the reporting format is in accordance with the simple form of “brand + sales”, according to the company’s requirements, each product now needs to be aggregated by sales time, the specific format As shown in columns B, C and D of the table (Figure 1). So, how can we complete the conversion of the format? The following Excel 2019 as an example to introduce the specific operation.


o Simple data splitting with the LOOKUP function

We can see that the final data is to be split quantitatively in column C according to the sales figures in the original data. For example, if there are 4 Xiaomi TVs, then they are arranged in 4 rows according to the sales time (billing time). For such quantitative repetitive data sorting, it can be done with the help of LOOKUP function.

Locate B2 and enter “Xiaomi B48H4K UHD TV”, switch to B3, then press Ctrl+E, the smart fill will extract the product name data to B2:B4. Similarly, enter “4 units” in C2, use the smart fill to extract the product name and sales data to C2:C4. The product sales data will be extracted to C2:C4, and then use Find and Replace to replace the word “units” with empty, and finally get the product name and sales quantity in columns B and C (Figure 2).



Here note that do not directly enter 4 in C2, because there are also numbers in the model number, smart fill will extract the wrong (will be extracted into 4, 5, 6). If there is no “station” in the original data of A2:A4 (such as A2 original data is “Xiaomi B48H4K Ultra HD TV 4”), then you can create a new auxiliary column B, enter “station” in B2 “fill, and then enter the formula “= A2 & B2” in C2. This way, using the above smart fill method again, you can extract the correct product name and sales figures in columns D and E. For data similar to mixed text and numbers, we should be good at processing the original data, so as to use intelligent filling to quickly extract the required data.


Position to D2 and enter “1”, D3 enter the formula “=C2+D2”, that is, cumulative, the formula drop-down fill to D4 after the display of cumulative figures, and then this number as a LOOKUP function reference to achieve quantitative repeat filling. Position to E2 and enter the formula “=LOOKUP(ROW(B1),$D$2:$D$5,$B$2:$B$4)”, and then pull down the fill to E10 can be (Figure 3).

Formula explanation.

ROW(B1): Here first use the ROW function to get the line number, and then the line number value as the value that LOOKUP needs to find, so that after pulling down to extract 1, 2, 3 line numbers in turn. $B$2:$B$4 is the value to be obtained, $D$2:$D$5 is the data area to be found, because the cumulative value is shown here, so LOOKUP will extract data according to the cumulative value, repeatedly extract the data of B2:B4 area to fill the E column, so as to achieve quantitative filling.

Finally, according to the example in Figure 1, insert a new column and enter the billing time, and in G2: G10 fill the number 1, will be A, B, C, D columns hidden, and finally complete the original data collation. Of course, here are formula references to the data, in order to facilitate the use, you can directly copy the E1: G10 data, after the new worksheet click “Start → Paste → selective paste → value” can be (Figure 4).


o Use SUM function to make data merging easier

The above operation is a simple summary of the data to be split, and then use the LOOKUP function to quantitatively repeat the filling of the split data. Very often, we may also need to perform the reverse operation, for example, the original data is in the format shown in Figure 4, and now we need to display a summary of sales for each product based on the date of sale (Figure 5).


For this operation, you can use the SUM function to sum up the sales data, and then use the hyphen to organize the data. Copy A2:A10 data to C2:C10, select C2:C10 data, click “Data → Remove duplicate values → Sort in the currently selected area”, click “Remove duplicate items”, so you can get in C2:C4 non-duplicate product name (Figure 6).


Position to D2, enter the formula “=SUMIF(A$2:A$10,C2,B$2:B$10)”, drop down to fill the formula to D5, so that you can achieve in D2:D4 for each product sales summary statistics (Figure 7).


Formula explanation.

A$2:A$10 is the summation condition area, and the summation condition is the data displayed in C2 (column C needs to eliminate duplicate data through the “delete duplicate” method, but the data should come from A$2:A$10). The actual summation area is B$2:B$10, so that the drop-down formula will be based on the C2:C4 conditions, the number of sales displayed in A$2:A$10 summation summary.



Finally, columns A and B will be hidden, and then insert the date column according to the format of Figure 5, so that the data is complete. If you need to organize the data into a format similar to the original data shown in Figure 1, then just enter “units” in F2:F4 and enter the formula “=D2&E2&F2” in G2. If you need to show the form of “x month x day sales xx units”, so as to facilitate reporting to the boss in WeChat, you can enter the formula “= TEXT (C2, “yyyy year mm month dd day”) & “sales” & D2 & E2 & F2 in H2 “, so copy the data in column F and paste it into WeChat (Figure 8). Note that because the date cannot be displayed directly after using the hyphen, here you need to use the TEXT function to set the date format.

Leave a Comment