You will need to sort the table again, this time by the last helper column, and the order shall be descending. Now that you have the results, copy them and paste as values. We're going to use the second helper column for another IF statement that will look for the complete book listing. The quickest way to apply the formula to every row is pointing to the bottom-right corner of the cell until you see a black plus sign and double-clicking it. One will merge the book titles if the values in the main column are the same. You will also need two helper columns for the formulas. One preliminary is sorting the table by your main column. The formulas take some time and effort, but they'll do the job if you have a simple table. It's quite a common task: if you want to keep one author per line yet list all their books in this column, there are two ways out I'm going to show you: a couple of formulas we can employ, and a special add-in designed for this. Things get a little trickier when it comes to text values. Click Ok and see the results right here.Īs you can see, work with numbers is designed very well in Excel, so you can easily combine numbers from duplicate rows using the provided tools. Select what function to use, and add Subtotal to the column with numbers that you have, Quantity in our case. Our key column goes for the first field here: 'At each change in' the author's name, You can also see the summary by selecting your data and clicking on Subtotal on the same tab. We add the reference and tick off 'Top row' and 'Left column' as our labels, and click Ok. Please note that you need to keep your key column leftmost because you want to use its values as labels.

You can change the function to any one you prefer and then select the range with your data. Go to Data tab and click on the Consolidate icon.

Now let's go back to our data and look at two more ways that are just as simple. Now you can say what rows you want to see here: the author's name is a key column in our example, and we need to take 'Quantity' for our values to see the number of books you have by each author. Choose where you want to see the results and click ok. Open the Insert tab, click Pivot Table, and make sure the selected range contains all your data. Perhaps the best way to go is to add a pivot table. Let's count the number of books by different authors we have in this list. When you deal with numbers, Excel has ready solutions for you. Consolidate duplicates in excel and sum numbers You may have text values associated with the same record, or unique numbers that you want to count. In this video we will learn how to keep unique values when merging duplicate rows in Microsoft Excel. How to combine duplicate rows in Excel: video transcript

How to merge cells in word 2016