See below example on spreadsheet with 6 or more columns of data. I would like to aggregate all checks under 1 row and delete the extras. Combine duplicate rows in excel I am trying to find an easy way to combine duplicate rows. See below example on spreadsheet with 6 or more columns of data.
Last updated: August 16, 2018 Spreadsheets in Excel are often used for storing data about customers and employees, and some of the most commonly used data fields in these situations are for their names. If your information is coming from a database, then it is likely that these names are separated into first and last names. But occasionally you will need to combine the first and last name in Excel, and the prospect of doing that manually is probably something you want to avoid. Fortunately there is a formula that you can use which will allow you to combine first and last names into one cell in Excel 2013. It is the CONCATENATE formula, and it can be very helpful in eliminating the need for a lot of unnecessary manual data entry.
Summary – How to combine first and last name in Excel. Click inside the cell where you want to display the combined names. Type =CONCATENATE(XX, ” “, YY) into the cell. Replace XX with the cell location of the first name, and replace YY with the cell location of the last name.
Press Enter on your keyboard to complete the formula. Copy the formula into the rest of the cells in the column, if necessary. We also explain the steps below with pictures, if you would prefer to see the instructions in that format. Merge First and Last Name Cells Into One Cell in Excel 2013 The steps below are going to use a formula to combine a cell with a first name and a cell with a last name into one cell. If you delete the original, separated cells, then the data in the combined cell will be deleted as well. If you want to delete the original cells and keep the combined cell, then you will need to use the option.
Step 1: Open your spreadsheet in Excel 2013. Step 2: Click inside the first cell in which you want to display the combined data. Step 3: Type =CONCATENATE(XX, ” “, YY) into the cell. Replace XX with the cell location of the first name, and replace YY with the cell location of the last name. Note that there is a space between the quotation marks in the middle part of the formula. This will insert a space between the combined first and last names.
In the example image below, the formula is =CONCATENATE(A2, ” “, B2). Step 4: Press Enter on your keyboard to execute the formula. Step 5: Click on the cell with the formula that you just created, click on the handle at the bottom-right corner of the cell, then drag it down to fill all of the cells for which you want to combine the names. Note that you can also copy the formula then paste it into these cells instead. Excel will automatically update the formula so that it displays the combined cells for that row.
Frequently Asked Questions Question 1 – Will the combined name update if I change the first or last name? Answer 1 – Yes, the cell containing the full name will update if you make a change to a first or last name in a cell. The full name cell is being populated by a formula, and part of that formula involves checking a specified cell for its current value. Question 2 – Is there a way that I can update the full name cell so that it won’t change if I make a change in one of the first or last name cells? Answer 2 – Yes, you can accomplish this by selecting all of the cells containing values that you don’t want to update, pressing Ctrl + C to copy them, then clicking the Paste button in the ribbon and selecting the Paste as Values option.
Question 3 – Can I split a column of full names into First Name and Last Name Cells? Answer 3 – Yes, you can do this with the Text to Columns option on the Data tab. This assumes that all of the names consist of just two words. Insert a blank column to the right of the column that contains the full name. Select the column containing the full name. Click the Data tab. Click the Text to Columns option.
Select the Delimited option, then click the Next button. Select only the Space option from the list of Delimiters, then click the Finish button. Note that you can end up with more than one extra column of data if any of the names are three words. Excel splits data into separate cells each time it encounters a space. If you have names with more than two words you will need to create an additional empty column for whatever the highest word count is among your full names. You will then need to combine these extra columns back into the appropriate columns for your needs.
Question 4 – Is there another formula that I can use to combine first and last names in Excel besides concatenate? Answer 4 – Yes, you can also combine first and last names in Excel using the formula below: =XX&” “&YY Note that the only space in the formula is between the ampersands. That space is what adds the space between the names in the full name field. Be sure to replace the XX part of the formula with the cell containing the first name, and the YY part of the formula with the cell containing the last name. There are a lot of other useful formulas in Excel 2013. To see what sorts of options you can employ to make some of your tasks a little easier.
Contents. The Scenario If you ever need to do any of the following:. Append Data. Consolidate Data. Combine Data that comes from either an Excel, text or csv file then this is the best solution for you to do so.
In this pattern you’ll get the most optimal and easiest way to combine your files from an specific folder and combine them all together if you’d like. That’s right! Combine data from a TXT, CSV and Excel all together. No limits on the size of the file or how many files you’d like to combine – it’ll simply work! Our Goal What we need is a way to extract all the data from our files like:. CSV Files.
Text Files. Excel Files and then somehow consolidate or append data in one tall table. This was a rather complex scenario that we could solve with VBA or SQL, but now we have a more efficient and user friendly way of doing this. Don’t forget to download the workbook in order to follow along! Step 1: Unzip the downloaded File and explore! Before we begin with the real pattern, make sure that you download the workbook in order to follow along. The workbook comes with the files that you can see in the previous image inside a folder called PQExample which is basically the folder where we’re going to point our Power Query solution to work on.
The file that contains the actual Power Query solution is called Ultimate Combination.xlsx and you should be able to see it immediately after you unzip the downloaded file. We encourage you to explore all files and see what’s in it. Overall, they all share the same column header names:.
Product. Date. Gross Sales. Amount Now we can head over to the real Pattern. Step 2: Find the Query that does the Magic Once you open the Ultimate Combination.xlsx file, you’ll notice that it has no data.
The Power Query solution has been stored as a connection only and its awaiting your command to load its data to your Excel workbook. In order to view this query, you’ll need to go to the Power Query ribbon, click on the Show Pane icon and then on the right side you’ll see the Query Pane with a query called Ultimate Combination. Right click that query and then click on Edit to open the Query Editor Window and analyze the solution. You’ll immediately notice that there is an error with the query, but don’t be alarmed. The reason behind such error its because the solution is pointing at my (Miguel) local folder instead of yours.
In order to fix that you’ll need to head over to the first step called Source and click on the gear icon right next to it. That should pop up a new window with a user friendly folder browser. Go ahead and find the folder you just unzipped called PQExample. And once you do that, you’ll notice that the solution will start to run and do its magic. You could end it here and call it a day since all the files were combined already (click on the step called Expanded to see the final result), but instead, we ‘re going to show how simple creating a solution like this was. Understanding the Query Instead of writing a long paragraph, we’ve divided this into sections so you can understand each step of the query on its own.
You can clic on the toggles to expand and view the content and collapse it back if you want. Here’s where we’ll need to get to know a bit about Power Query functions. We know that we have some Excel files in our query but, how do we extract the data from them? Take a look at the columns that we have available. You’ll notice that we have a column called Content that holds a binary.
That binary is the actual Excel file and in order to interpret that binary we need a function called Excel.Workbook. Using the following formula: if Text.StartsWith(Extension, '.xls') then Excel.Workbook( Content) else null we get a new column that is basically the one that shows me all the data that the Excel file (on each row) holds. You can click on any of the Table values found on the Custom column to find out what’s inside of each of them.
More often, we deal with 3 different kinds of Data inside an Excel Workbook:. A Sheet.
A Table. A Named Range Note: You’ll notice that in some files we’ll have tables, sheets and in others we’ll only have sheets.
This is the moment where we define if we just want to combine the tables, the sheets, the named ranges or a combination of them. Be sure to check that you’re not combining the same data twice as a table is part of a sheet and could potentially get combined in the wrong way. Now we need to expand that Custom column so we can get all the Excel data in our Query and choose the ones that we want. The result of that gives us 4 new columns:. Custom.Data = the actual data found inside the Excel workbook (represented as a table).
Custom.Name = the name of the worksheet where the data is stored. Custom.Item = this is the name of the item, if it’s a named Range then it’ll be the name of that range, for Sheets then it’ll be the name of the Sheet and for Tables it’ll be the name of the table.
Custom.Kind = it’s the name of the object that was found from the excel workbook. Most commons are Sheet, Table and DefinedName. The Formula and other steps So far we managed to extract the data from the Excel workbook into objects. Now we need to find a way to extract the data from the CSV and TXT file and being able to combine that with the data found in the Excel file. We’ll create a new column with this formula that will do the trick: if Extension = '.csv' then Table.PromoteHeaders(Csv.Document(Content)) else if Extension = '.txt' then Table.PromoteHeaders(Csv.Document(Content,null,',' )) else if Custom.Kind 'Table' then Table.PromoteHeaders(Custom.Data) else Custom.Data Note: we are assuming that the txt file is delimited by a comma, but you can change that by changing the comma in this line of code for something else like a pipe ( ), bars (/ ), semicolon or other. Csv.Document(Content,null,',' )) The result of that formula gives us a column that holds only tables with the correct headers. You might be tempted to expand this column right now, but we won’t do that just yet.
Instead, we’ll clean the table that we currently have and delete unnecessary columns like Content and Custom.Data since all the data that I need is stored on the Custom column. We will also add a new column that will give us some important information, total row count, about the tables that we’re about to combine. Using this formula we get the total row count for those tables: Table.RowCount(Custom) and this is what you see at the Almost there step.