MERGE CELLS ON EXCEL FREE
Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. But if you do, then you'll have to extend the Array formula onto more columns. Excel doesn’t support copy multiple ranges at once, but, with Kutools for Excel’s Copy Ranges feature, you can copy multiple selected merged cells into single cells immediately. I have assumed that you'll never need to join more than 7 values together (eg the 7 days of the week). That may suit you needs as is, but if you insist on comma separating the days, then you can add this to K2, and just hide columns B to J. Select the Cells that you want to merge, click on the Home tab and then click on Merge button. This takes your data in column B offsets it by the first row occurrence (Step 3), re-sized by the count of names (Step 2), and transposed (which reverses rows for columns) =TRANSPOSE(OFFSET(Sheet1!$B$1:$B$5,C2-1,0,B2)) This is quite useful when we need to assign one value of the cell to 2 or more cells. If you've not worked with Array Formula before then: (i) select cells D2:J2 (ii) ensuring D2 is the active cell (iii) enter the formula below into the formula bar (iv) press ctrl+shift+enter Merge cells in excel is used for merging more than one cell. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas. Step 4) On Columns D to J you'll need to add the following Array Formula. To split a cell in Excel, add a new column, change the column widths and merge cells. To merge cells, select the cells that you want to merge and then click the Merge & Center button. (IMPORTANT: this means your Raw data will need to have a Sort applied to ensure the Names all appear grouped together) =MATCH($A2,Sheet1!$A$1:$A$5,0) Choose Merge & Center control on the Ribbon, which is simpler. This will find the row number of the first occurrence of the name. (Note: I'm assuming your Raw data is in Sheet1, and the new copy to process in Sheet2) =COUNTIF(Sheet1!$A$1:$A$5,$A2) Split text from one cell into multiple cells. If you need to reverse a cell merge, click onto the merged cell and then choose Unmerge Cells item in the Merge & Center menu (see the figure above). This will count the number of occurrences of each Name. To merge cells without centering, click the arrow next to Merge and Center, and then click Merge Across or Merge Cells. Step 2) On your new sheet add the following into column B2. Step 1) Separate out all your unique names in column A into a new sheet, again into column A on your new sheet - if your using excel 2007 or later you can just copy the whole column over then use the Remove Duplicates tool (I'm hoping I'v understood your setup correctly)