How to merge data into one cell in Excel

This article brings you relevant knowledge about excel, which mainly introduces the related issues about merging multiple data into one cell. There are always some special problems in work. Requirements, the most troublesome thing is to put all the multiple results that meet the conditions into one cell. Let’s take a look at it. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

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

For example, look at 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.

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 and fill downward 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 the sum of A2 and Whether the values ​​of A1 are equal, if equal, return C1&","&B2, if not, 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 Equality, returns true value C2&”,”&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 auxiliary column formula input is completed, a commonly used 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/(condition area=specified condition), target area to be returned)

This formula starts with 0/(E2=$A$2:$A$9 ) constructs a memory array composed of 0 and error value #DIV/0!, and then uses 1, which is always greater than 0, as the search value, and then finds the last column C result that satisfies the department equal to E2, that is, the last advertisement in column A The value in column C corresponding to the part: C2.

If you are using Excel 2019 and above, you can use the TEXTJOIN function. This function is also available in the latest version of the WPS table. 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 cells, the content to be merged)

In the formula The merged content is:

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

That is, if A$2:A$9 is equal to E2, return B $2: The content corresponding to B$9, otherwise the empty text "" is returned, and the result is a 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 merge data into one cell in Excel. For more information, please follow other related articles on the PHP Chinese website!