Combining duplicate entries in '08 Excel

Hi Chris, this is pretty easy to do with Pivot Tables, which will summarise the data in the table for you. It will add multiple instances of the repeated words and sum the frequency, and give you a nice summarised table.

To create a pivot table make sure that you have a heading directly above your first line of data, and at the bottom highlight in yellow the first blank row at the bottom of your table. This is to remind you insert data between the last row of data and the highlighted rows. As we are going to create a named range for the data, so that as you add more data to the table the named range will be updated as well.

See the screenshot below of how the source data table should look



Ok we need to define a name for your data. Select the entire table including the heading and the blank row at the bottom of the table (the one highlighted in yellow). Click on the Name Range box, just above Col A as shown below, and enter a suitable name, I used 'Data'.



Now we are going to create the pivot table, I am using Excel 2011, but you can use 2008, although I am a little rusty on how it used to work, but I am sure you can work it out if I am a little off. From the Data menu choose Pivot Table (Might be called something slightly different in 2008 Pivot Table Wizard or something). Then in the Dialog where it asks you for a range enter the name of the range 'Data'.



I think from memory 2008 has a step by step wizard that may then ask you if you want to create on a separate sheet or on the same sheet. I find it easier to create on a separate sheet.

Ok the next part in 2008, it is a bit different from 2011, and you will be presented with a grid for the pivot table, you need to drag the Word (names) to the Row Labels and the frequency to 'values'.



Hopefully you will see a summarised table similar to the above.

When you add more date you need to insert data into the source data table between the last row and the first blank row. Just insert the number of blank rows that you need if you are typing this in manually. That way the named range 'Data' will automatically expand. To check that this is correctly picking up all the data you can click on the named range/ cell box enter 'Data' and it will show you the named range.

You will need to refresh the pivot table to get it to update as you add new data. Right click on the pivot table and choose refresh.



I hope that helps - Note you are not deleting the duplicated rows in the source table, but you can copy and paste as values the pivot table to have a nice summary of your word count.

Hope this helps.

 

Hi Chris, sorry for the delay in replying. Now I see your source data I can see the problem. You need to get your data into a format that works better with Pivot Tables. The problem is you have multiple columns for data that you want to sum (the different books). You need to do one of the following

1. Add a total column that sums the total number of word counts over all the books. If Column F was the last column then add a column in Column H called 'Word Total' and have a formula that sums Columns B to F and copy it all the way down to the bottom of the table. I know this may appear odd as each line may only be summing one value, but we want to have just one Column for the frequency in the pivot table. The Copy and paste as values the first column AAAWord and the Total column to a separate tab on the spreadsheet and build the pivot table from that. The downside to this is, it is not so easy to see the books that are driving the final results and you will have to recreate the summary table each time you add additional data.

EDIT - Actually you don't need to create a separate table - you just need to add the total column to the value part of the pivot table, and ignore the individual book columns (but you will need to select the entire table range when first creating the pivot table). This is probably easier than recreating the data table in 2 below, although the suggestion in 2 is a neater approach.

or

2. Create a table that has the data in the format

Three Columns

Word Name , Name of Book, Frequency

It will not take long to build from your data. Just copy the first column (names) to a new sheet into Col A, then paste the name of the first book AAA American Cookery (1798) into the second column, from the first line down to the last row of names that you have in Col A, then paste the frequency values in from Col B for the first book into Column C. Now sort this new table by Frequency and delete all the rows with nothing in Col C.

Move to the bottom of the table and repeat the process for all the other books.

You will end up with a table that looks a little like this



Then when you create a pivot table it will hopefully give you the result that you want



If you use this approach you can double click on a pivot table result value and Excel will create a new tab with the source data, so you can easily see the books that are generating the data.



I am sure you may have already solved your problem, but hopefully this will help for the future.