Combine duplicatas no Excel, some algumas colunas e a média ponderada as outras

Question

Eu tenho uma grande planilha do Excel com linhas duplicadas, preciso condensar as duplicatas em uma linha com algumas das colunas somadas e as outras ponderadas em média. Isso é possível? É uma lista de termos de pesquisa do Google e alguns dos termos são exibidos dez vezes em outros apenas uma vez, mas é importante manter os dados precisos ao mesclar.

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

Eu quero converter o acima no abaixo sem fazer manualmente? I.E. exclua as duplicatas e some B, C, F e H e faça uma média ponderada em D, E, G, I e J.

Isso faz parte de uma planilha muito grande.

advent calendar 25914 718680    0.04    0.13    3401.94 4.68    1351    2.68    0.05

Eu poderia me contentar com apenas uma média de D, E, G, I e J

Eu calculo uma média ponderada dividindo os cliques nesses dados de linha pelo total de cliques da palavra duplicada, multiplicando essa fração pelos dados da célula e somando a coluna de duplicatas. A soma do total ficou assim: = ($ 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)

Cometi um erro no meu pedido , para a coluna £ / conv I. NÃO quer ter uma média ponderada pelos cliques que deseja ponderar pelas conversões OU como o fiz , calculando depois a partir do custo total dividido pelo total de conversões (eu lidei com ele antes de executar a macro, excluindo os números e colocando em zeros).

    

Answer 1

Cada linha de código é comentada. Por isso é fácil adaptar o código para tarefas semelhantes

O que faz

• Essa macro VBA combina todas as linhas exclusivas em uma planilha copiada.
  Ele analisa os valores da coluna A para decidir qual coluna é um dublicado
• Soma as colunas B, C, F e H.
• calcula a média para D, E, G, I e J.
  ^{Ele não usa uma média ponderada, pois eu ainda não sei exatamente como você os calcula}

Como usar

• abra sua pasta de trabalho de dados e pressione ALT + F11
• copiar & cole o código abaixo em algum lugar ou em um novo modul
• personalize AVcols() e SUMcols() se você quiser calcular a soma ou a média em outras colunas
• feche o editor do VBA e selecione / visualize a planilha que deseja combinar
• pressione ALT + F8 e execute a 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

Dê uma olhada na minha pasta de trabalho de teste se tiver problemas para iniciar a macro.

    

Answer 2

Se você puder colocar as informações resultantes em outra planilha ...

Copie a coluna A para a nova planilha, selecione sua nova coluna e vá para Data / Remove Duplicates (ou selecione dados e pressione Alt + A , M ).

Para dados que precisam ser

• somado, =SUMIF(OriginalData!A:A,NewData!A2,OriginalData!B:B)
• média ponderada, SUMPRODUCT(OldData!B2:B1000,OriginalData!D2:D1000,--(OriginalData!A2:A1000=NewData!A2)/SUMIF(OriginalData!A:A,OriginalData!A2,OriginalData!B:B)
  • observe que você pode substituir uma célula da sua nova planilha pelo SUMIF, por acaso é a mesma