![how to create a name list by merging cells in excel how to create a name list by merging cells in excel](https://trumpexcel.com/wp-content/uploads/2015/10/How-to-Merge-Cells-in-Excel-Pop-Up-Message.gif)
- How to create a name list by merging cells in excel code#
- How to create a name list by merging cells in excel free#
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.ĭo you need help adapting this to your needs?
How to create a name list by merging cells in excel free#
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.Īutomate Excel so that you can save time and stop doing the jobs a trained monkey could do. Power Query – Lookup Values Using Merge.Power Query – Split delimited cells into rows.Related pages which may be useful to you are: Having to change the M Code, is a bit trickier than we might like, but it’s still significantly easier than in standard Excel. What would have been a complex scenario, has been made simple with Power Query. In the Import Data dialog box, select to load a table into cell F1 of the existing worksheet.Ĭlick OK to close the Import Data options box. The Power Query Editor will close, and the view returns to Excel. Let’s push the table back into Excel.Ĭlick Home -> Close & Load (drop-down) -> Close and Load To… Now the preview window has reduced each person’s equipment down to a single cell.
How to create a name list by merging cells in excel code#
Which means in the formula bar, we change the M code from this: = Table.Group(#"Merged Columns", ) So, if we wanted to separate by a semi-colon ( ) and a space character, the formula would be: Text.Combine(, " ")
![how to create a name list by merging cells in excel how to create a name list by merging cells in excel](https://www.repairmsexcel.com/blog/wp-content/uploads/2018/06/merge-2-cells-with-space.png)
Power Query’s formula bar is like the one in Excel. If it is not, click View -> Formula Bar to enable it. Hopefully, the formula bar is visible between the ribbon and the data preview window. Next, we are going to edit the M code in the formula bar. We will use Sum as a placeholder function, then edit the generated M code to change the List.Sum function for a Text.Combine function.Īs you can see below, our column now contains errors. The operation drop-down has many options: Sum, Average, Median, Min, Max, Count, Count Distinct Rows, and All Rows. This creates an error, as we cannot sum text. Column: Select the column to be reduced from rows into a single cell (Equipment.1 in our example)Įquipment is a text column, and we have selected the Sum operation.Operation: Select Sum from the drop-down list.New column name: Enter a name column of combined cells (I’ve used Equipment).Select all the columns, except the column to be combined, in our example, we need to select the First Name and Last Name columns. But don’t worry, because I’ll explain why it occurs and what we do about it. In our next transformation, we will create an error. The Equipment and Equipment Attribute columns have now been combined into one, as shown by the screenshot below. I’ve given the new column the name Equipment then we can click OK. So, we need to select –Custom– from the Separator drop-down list, and in the box below enter Colon ( : ), followed by a space character. Select the Equipment and Equipment Attribute columns, then click Transform -> Merge Columnsįor our example, we want to place a colon and space between the two column values. Now we’re ready to start the transformations.įirst, we will combine Equipment and Equipment Attribute into a single column, then secondly, combine the rows into a single cell for each individual. The Power Query window will open and show the data from the table. Select a cell within the data table, then click Data -> From Table / Range
![how to create a name list by merging cells in excel how to create a name list by merging cells in excel](https://www.excelhow.net/wp-content/uploads/2019/08/extract-initials-from-a-name1.png)
But with Power Query, it becomes a few simple transformations. This all sounds simple, doesn’t it? Before Power Query, we would need to use complex formulas to achieve this result.
![how to create a name list by merging cells in excel how to create a name list by merging cells in excel](https://media.gcflearnfree.org/ctassets/topics/248/concat_new_fill.png)
The goal is to combine all the equipment information for each employee into a single cell (as shown below). We can see that Anna Watkins has a laptop, access card, phone, and home printer. In the screenshot above, we have a list of employees, along with any equipment the company has allocated to them. We will be using this throughout the post.