# Combine duplicates in Excel, sum some columns and weighted average the others

I have a large excel spreadsheet with duplicated rows, I need to condense the duplicates into one line with some of the columns summed and the others weighted averaged. Is that possible? It is a list of Google search terms and some of the terms are shown ten times others only once but is is important to keep the data accurate when merging.

```A................B.......C..........D......E......F..........G.....H.....I.......J

Term............Clicks..Impre...AvCTR...AvBid...Cost....AvPos..Conv.£Conv..CRate

advent calendar 5572    147355  0.04    0.12    658.01  5.21    328 2.01    0.06

advent calendar 5719    084078  0.07    0.15    883.38  4.05    234 3.78    0.04

advent calendar 3398    070341  0.05    0.16    540.52  5.58    226 2.39    0.07

advent calendar 3078    108016  0.03    0.09    274.54  4.69    194 1.42    0.06

advent calendar 4948    140714  0.04    0.13    619.11  4.02    184 3.36    0.04

advent calendar 2193    088628  0.02    0.14    312.10  4.69    142 2.20    0.06

advent calendar 0861    077904  0.01    0.11    097.07  5.89    036 2.70    0.04

advent calendar 0104    000635  0.16    0.12    012.63  2.03    004 3.16    0.04

advent calendar 0034    000927  0.04    0.11    003.82  4.53    002 1.91    0.06

advent calendar 0007    000082  0.09    0.11    000.76  5.09    001 0.76    0.14
```

I want to convert the above into the below without doing it manually? I.E. delete the duplicates and sum B, C, F and H and do a weighted average on D, E, G, I and J.

This is part of a very large spreadsheet.

```advent calendar 25914 718680    0.04    0.13    3401.94 4.68    1351    2.68    0.05
```

I could settle for just an average of D, E, G, I and J

I calculate a weighted average by dividing the clicks in that rows data by the total of clicks for that duplicated word, multiplying that fraction by the cell data and then summing the column of duplicates. The sum for the total looked like this: =(\$B2/\$B15*E2)+(\$B3/\$B15*E3)+(\$B4/\$B15*E4)+(\$B5/\$B15*E5)+(\$B6/\$B15*E6)+(\$B7/\$B15‌​*E7)+(\$B8/\$B15*E8)+(\$B9/\$B15*E9)+(\$B10/\$B15*E10)+(\$B11/\$B15*E11)

I have made a mistake in my request, for the £/conv column I. It does NOT want to be an average weighted by clicks it wants to be weighted by conversions OR as I did it, by calculating afterwards from the total cost divided by total conversions(I dealt with it before I ran the macro by deleting the figures and putting in zeros).

-

## migrated from stackoverflow.comJan 25 '13 at 17:21

This question came from our site for professional and enthusiast programmers.

Thank you, and apologies! – RichardR Jan 25 '13 at 14:36
I'm no Excel expert, so not sure if this is possible using it, but it's pretty standard stuff for a database tool. You might want to look at a product like Microsoft Access that's suprisingly easy to use without too much technical knowledge, and the investment in learning it could be well worth the effort if you're working in an analysis driven job like PPC advertising. You can probably download a trial of Access to see if it's suitable for you, or you could look at a free tool like OpenOffice Base; however, I think MS Office is much more polished and easy to use than OpenOffice. – Ewan Heming Jan 25 '13 at 15:06
Thanks Ewan, I will look at Access now. – RichardR Jan 25 '13 at 15:14

### Each code line is commented. So its easy to adapt the code for similar tasks

What it does

• This VBA macro combines all unique rows in a copied sheet.
It looks at column A values to decide which column is a dublicate
• It sums up the columns B, C, F and H.
• it calculates the average for D, E, G, I and J.
It doesn't use a weighted average since I still don't know how exactly you calculate them

How to use

• open your data workbook and press ALT+F11
• copy & paste the code below somewhere or in a new modul
• customize `AVcols()` and `SUMcols()` if you want to calculate the sum or average in other columns
• close the VBA editor and select/view the sheet you want to combine
• press ALT+F8 and execute the macro `combineduplicates`

``````Sub combineduplicates()                 '### starts our macro
Application.ScreenUpdating = False      '### Excel wont update its screen while executing this macro. This is a huge performace boost
Dim AVcols()                            '### declare an empty array for our average columns
Dim SUMcols()                           '### declare a second empty array for our sum columns
Dim AVtemp()                            '### declare a third empty array for our temporal values we need to calculate a weighted average

AVcols() = Array(4, 5, 7, 9, 10)        '### we use the first array to store our columns for calculating an average
SUMcols() = Array(2, 3, 6, 8)           '### the second array stores the columns which should be summed up
Mcol = 2                                '### whats the multiplier column for our weighted average?

ActiveSheet.Copy Before:=Sheets(1)      '### take a copy of our activesheet. this way we don't touch the original data
'### the next line sets our range for searching dublicates. Starting at cell A2 and ending at the last used cell in column A
Set searchrange = Range([A2], Columns(1).Find(what:="*", after:=[A1], searchdirection:=xlPrevious))
For Each cell In searchrange            '### now we start looping through each cell of our searchrange

ReDim AVtemp(UBound(AVcols) + 1, 0) '### make our temp array 2-dimensional and reser it from the previous loop
For i = 0 To UBound(AVcols)         '### save values from start row for average calculating into the temp array
AVtemp(i, UBound(AVtemp, 2)) = CDbl(Cells(cell.Row, AVcols(i)))     '### still filling the temp array
Next i                              '### go ahead to the next column
AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2)) = CDbl(Cells(cell.Row, Mcol)) '### save the clicks too

Set search = searchrange.Find(cell, after:=cell, lookat:=xlWhole)   '### searches for a dublicate. If no dub exists, it finds only itself
Do While search.Address <> cell.Address     '### until we find our starting cell again, these rows are all dublicates

For i = 0 To UBound(SUMcols)    '### loop through all columns for calculating the sum
'### next line sums up the cell in our starting row and its counterpart in its dublicate row
Cells(cell.Row, SUMcols(i)) = CDbl(Cells(cell.Row, SUMcols(i))) + CDbl(Cells(search.Row, SUMcols(i)))
Next i                          '### go ahead to the next column

ReDim Preserve AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2) + 1)    '### expand the temp array so we have enough space to fill with values
For i = 0 To UBound(AVcols)     '### loop through all columns for calculating the weighted average
'### the next line saves the value in our temp array, but now for the duplicate rows
AVtemp(i, UBound(AVtemp, 2)) = CDbl(Cells(search.Row, AVcols(i)))
Next i                          '### go ahead to the next column
AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2)) = CDbl(Cells(search.Row, Mcol))   '### save the clicks too

search.EntireRow.Delete         '### we are finished with this row. Delete the whole row
Set search = searchrange.Find(cell, after:=cell)    '### and search the next dublicate after our starting row
Loop

If search.Row = cell.Row Then       '### ok, now we have to calculate the average. All needed values are temporarly stored in our temp array
For i = 0 To UBound(AVcols)     '### start with looping through all average columns
average = 0                 '### reset the variable from the last loop
For j = 0 To UBound(AVtemp, 2)              '### start looping through the data from all dublicated rows
clicks = AVtemp(UBound(AVcols) + 1, j)  '### take the clicks for that row from the array
sumclicks = Cells(cell.Row, Mcol)       '### take the summed up  clicks for all dublicated rows
addaverage = AVtemp(i, j)               '### take the value which should be multiplied
average = average + (clicks / sumclicks * addaverage)   '### now calculate the weighted average and sum it up with the old one
Next j                      '### goto next data of dublicate rows
Cells(cell.Row, AVcols(i)) = average    '### when finished with calculating, write the result to the workbook
Next i                          '### go ahead to the next average column
End If                              '### only the end line of our condition

Next                                    '### from here we start over with the next cell of our searchrange
'### Note: This is a NEW unique value since we already deleted all old dublicates
Application.ScreenUpdating = True       '### re-enable our screen updating
End Sub                                 '### ends our macro
``````

Have a look at my test workbook if you have trouble to start the macro.

-
Dear Nixda, this is brilliant. Amazing it has enabled me to merge the data from over a thousand duplicates and make my next few weeks work so much better. THANK YOU. Well done. – RichardR Jan 28 '13 at 14:54
I'm glad it helped. if you show how you calculate the weighted average I can a help a little bit more. With what columns are the "average values" multiplied? Edit your initial question if you want so. – nixda Jan 28 '13 at 14:58
I calculate a weighted average by dividing the clicks in that rows data by the total of clicks for that duplicated word, multiplying that fraction by the cell data and then summing the column of duplicates. The sum for the total looked like this: =(\$B2/\$B15*E2)+(\$B3/\$B15*E3)+(\$B4/\$B15*E4)+(\$B5/\$B15*E5)+(\$B6/\$B15*E6)+(\$B7/\$B15‌​*E7)+(\$B8/\$B15*E8)+(\$B9/\$B15*E9)+(\$B10/\$B15*E10)+(\$B11/\$B15*E11) This sum was on a test sheet that had ten rows. Do you think you could get this into the macro? Cheers Richard – RichardR Jan 28 '13 at 15:09
Done. See the edit. I replaced the average calculation with a weighted average calculating – nixda Jan 28 '13 at 20:30
Hi, amazing. You would not believe the trouble I had with this, it was giving me a run time error Overflow message, however I cut the sheet down in size. Until I sorted the spread sheet by Clicks! Then it ran fine, it did not like the sheet in either search term or conversion order. Thank you again. There must be others out there with Google reports like mine! – RichardR Jan 30 '13 at 12:38

If you can stand putting the resulting information on another sheet...

Copy column A to the new sheet, then select your new column and go to Data / Remove Duplicates (or select data and press Alt + A, M).

For data that needs to be

• summed, `=SUMIF(OriginalData!A:A,NewData!A2,OriginalData!B:B)`
• weighted average, `SUMPRODUCT(OldData!B2:B1000,OriginalData!D2:D1000,--(OriginalData!A2:A1000=NewData!A2)/SUMIF(OriginalData!A:A,OriginalData!A2,OriginalData!B:B)`
• note that you can substitute a cell from your new sheet for the SUMIF it it happens to be the same
-
I will happily put the info on another sheet. I can see how to remove the duplicates but I can't understand what you mean after that? My search report has 5000 rows each with a search term and its data, some of those search terms are identical but have individual data. I need to get to unique terms in the sheet but I need to have all the data that was associated with the duplicates that have been deleted, i.e. in the above example I need one row with "advent calendar" and the total clicks, total impressions etc garnered from the deleted rows? cheers Richard (lives in Cheshire, UK!) Weird? – RichardR Jan 25 '13 at 17:40
now you have your list of data (without duplicates in column A of the new sheet, you can reference that in adding up all the totals from the old data. e.g. assuming `advent calendar` is in A2, then `=SUMIF(OldData!A:A,newData!A2,OldData!B:B)` would give you a total of all the numbers in column B from your original sheet where it has a corresponding `advent calendar` in column A of the original sheet – SeanC Jan 25 '13 at 17:46
Thank you for your help, I was unable to make this work. However one of your forum friends has produced a macro that did the job perfectly. – RichardR Jan 28 '13 at 14:55