Merging the same criteria cell data

The author of the unit this year has implemented the National Computer Application grade examination, according to the promotion job level different, examinee may choose 2 to 4 modules examination. Each examinee has the unique file number, the same examination qualified 1 and above module, obtains 1 Qualifications certificate, the certificate marking examinee qualified module.

If the candidate Liu XX qualified 1, there are 1 certificate, the examinee Wang xx qualified 3 families, also obtained a Certificate of conformity, certificate showed the Qualified 3 modules (Figure 1). In order to facilitate inquiries and to facilitate candidates to receive the card signature, the same file number of candidates in Figure 1 of the qualified modules to be merged, the module with "/" separated (Figure 2), then how to achieve this effect? Because the same file number has 2, 3, and 4 three cases, the number of modules to be merged is not fixed, and an array formula is needed to resolve such problems.

Figure 1 Three modules of the same file number in an Excel table

Figure 2 Merging excle the same file

The specific methods are as follows:

First, define the name

To simplify the formula, you need to set the name for the reference cell locale.

Execute the insert → name → definition command, open the Define Name dialog box, define the name as "Date", and enter the formula in the Reference location text box: = sheet1! $C: $C, press OK to return. Then use the same method, and then define another name called "Code", which includes the formula: = sheet1! $A $: $A $1200, which is the data region where column A is located.

Second, the input formula

First, the various input formulas in the E2 unit:

"=if (Iserr (Data,small (code= $A 2,row (code), 1))," ", INDEX ($C: $C, SMALL (if (code= $A 2,row (code), 1)) & ") ”。 The formula first judges a column, and if the A2 cell content exists in the code area, returns the cell cells number and, if there is a duplicate, the smallest line number. The function index () is used to select the contents of the data area cell for the row and separate it with "/". To avoid error values, the information function iserr () is used here, or blank if an error value is present, otherwise the content displayed for the formula. When the formula is finished, press the "Ctrl+shift+enter" key combination. Because the same file number is up to 4, you also need to take the 2nd, 3rd, and 4th small line numbers. So you also need to enter the array formula in the F2, G2, and H2 cells:

F2 Cell: =if (Iserr ($C: $C, SMALL (if ($A $: $A $21= $A 2,row ($A $: $A $21)), "", Index ($C: $C, SMALL (if ($A $ $A $21=$ A2,row ($A $ $A $21)), 2) & "/") G2 Cell: =if (Iserr ($C: $C, SMALL (IF ($A $: $A $21= $A 2,row ($A $: $A $21)), "", INDEX ($C: $C, SMALL (IF ($A $ $A $21= $A 2,row ($A $: $A $21)) & "/")

H2 Cell: =if (Iserr ($C: $C, SMALL (if ($A $: $A $21= $A 2,row ($A $: $A $21)), "", Index ($C: $C, SMALL (if ($A $ $A $21=$ A2,row ($A $: $A $21)), 4))

After the formula is finished, press the "Ctrl+shift+enter" key to end the formula entry. Then enter the formula in cell D2: "=e2&f2&g2&h2". Connect the contents of the E2, F2, G2, H2 cells. At this point you can see qualified 1 to 3 modules of candidates, cell content merged, the end of more than the symbol "/", but also to remove this extra symbol. Accordingly, enter the formula in the I2 cell: =if (Right (d2,1) = "/", MID (D2,1,len (D2)-1), D2).

When all the formula input is complete, select the D2:i2 range by dragging and copying the formula to the appropriate area. This completes the merging of the corresponding data. Finally, you need to delete the formula by copying all the areas with the formula, performing the edit → Paste Special command, and selecting the Value option in the Paste Special dialog box.

After you have done this, you are still not up to the effect of Figure 2, and you need to use a formula to delete duplicate rows of data. Therefore, enter the formula in the J2 cell: =if (A2<>A3,A2, ""). and copy to the corresponding data area, then use sorting or filtering method to delete the J column blank area, the data region for appropriate processing to complete the effect shown in Figure 2.