How to put qualified results into a cell in Excel

This article brings you relevant knowledge about excel. It mainly organizes related issues on how to put qualified results into a cell. Let’s take a look at it together. I hope it will help Everyone is helpful.

Related learning recommendations: excel tutorial

There are always some weird and special needs at work, and the most troublesome ones are Too many results that meet the conditions are put into one cell.

For example, please see the picture below.

Column A is the name of a company department, and column B is the name of the person.

It is required to fill in the names of personnel in the same department into the corresponding cells in column F, with commas separating different names.

Seeing this, someone must be muttering in their hearts:

Boy, your data processing is not standardized. How can you put so many people's names in one cell? This is against the laws of the data. Go for it...

Stop! ! ——

As a member of the army of cousins, I know better that the difference between life and death in the form data is never mine, but the boss who always has a straight face.

Back to business, let’s talk about the solution to this problem:

First enter the formula in C2:

=IF(A2=A1,C1&”,”&B2,B2)

Copy the fill downwards.

F2 input formula:

=LOOKUP(1,0/(E2=$A$2:$A$9),C$2:C$9)

Copy the fill downwards to get the final result.

This solution uses the auxiliary column method.

C column is an auxiliary column, which is a simple IF function.

Take the formula of C2 as an example:

=IF(A2=A1,C1&”,”&B2,B2)

First determine whether the values ​​of A2 and A1 are equal. If equal, return C1&","&B2, if not equal, return B2.

The values ​​of A2 and A1 are not equal here, so the formula returns the value of B2 "Zhu Hongzhong".

When the formula is copied and filled downwards, the result obtained by the formula will be used by the next formula below the cell where the formula is located, thus forming the effect of accumulating names.

For example, the formula in cell C3:

=IF(A3=A2,C2&”,”&B3,B3)

The values ​​of A3 and A2 are equal, and the true value C2& is returned. ","&B3.

C2 is the result B2 (Zhu Hongzhong) returned by the previous formula. The value of B3 is "Xingguang", so the final result of C3 is "Zhu Hongzhong, Xingguang".

After the input of the auxiliary column formula is completed, a common LOOKUP function routine is used in column F to obtain the final result:

=LOOKUP(1,0/(E2=$A$2:$ A$9),C$2:C$9)

This routine of LOOKUP ignores the error value and always obtains the last qualified result. We can summarize it as:

=LOOKUP(1 ,0/(conditional area=specified condition), target area to be returned)

This formula uses 0/(E2=$A$2:$A$9) to construct a 0 and error value#DIV/ 0! memory array, and then use 1, which is always greater than 0, as the search value, and then find the last column C result that satisfies the department equal to E2, that is, the column C value corresponding to the last advertising department in column A: C2.

If you are using Excel2019 or Office365, you can use the TEXTJOIN function. This function is also available in WPS2019.

Enter the following formula in cell F2, hold down SHift Ctrl, press Enter, and OK.

=TEXTJOIN(“,”,1,IF(A$2:A$9=E2,B$2:B$9,””))

The usage of the TEXTJOIN function is:

=TEXTJOIN (spacing symbol, whether to ignore empty text, the content to be merged)

The content to be merged in the formula is:

IF (A$2:A$9=E2,B$2:B$9,””)

That is, if A$2:A$9 is equal to E2, return the content corresponding to B$2:B$9, otherwise return empty text "", the result is a legendary memory array:

{"Zhu Hongzhong";"Xingguang";"";"";"";"";"";""}

## The #TEXTJOIN function merges the memory arrays obtained by the IF function. The first parameter specifies the comma as the separator symbol, and the second parameter uses 1, which means that empty text in the memory array is ignored.

Related learning recommendations:

excel tutorial

The above is the detailed content of How to put qualified results into a cell in Excel. For more information, please follow other related articles on the PHP Chinese website!