Solution:
As the original data, column A of the body can be compared with column C. If column A cannot be found, it is not required (as shown in the figure below). If there is less data, you can delete it one by one. What if there is A lot of data?
Method 1: Simple functions and skills are valid: recommended for general operators
The function is very simple, as shown in the following figure, select cell G2, copy the following formula into the cell, and then fill it down to cell G14:
= VLOOKUP (A2, C: C, 1 ,)
You can also use a unique number, as shown in column H below:
= SUMIF (C: C, A2)
However, you will find that the results after using the function are not very good:
You also need to perform a filtering operation:
Filter out the result of # NA (VLOOKUP result) (or if the SUMIF result of column H is 0:
We can see the final result in column:
Method 2: Pure function method: suitable for function Magic
ENTER the following content in E2, CTRL + SHIFT + ENTER, and then fill in the lower direction to achieve the effect.
= OFFSET (A $1, SMALL (IF (ISNA (MATCH (A $2: A $14, C $2: C $3,), ROW ($1: $13), 14), ROW (A1 )),)&""
Of course, the functions here do not have to use OFFSET, INDEX, and INDIRECT.
