Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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).

share|improve this question

migrated from stackoverflow.com Jan 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

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
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
share|improve this answer
    
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 –  Sean Cheshire 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.