Questions

excel formula to find duplicates and sum their corresponding cells

+
0 Votes
Locked

excel formula to find duplicates and sum their corresponding cells

knowledgebased
The worksheet looks like this (pipe | for the next column):

Column A | Column B
jeff | 1
jeff | 21
lara | 4
mike | 300
mike | 500
kate | 6

I want a formula that will seek out duplicate values in Column A and then sum the values that exist in Column B for each duplicate.

In this example I would like my final result to show Jeff | 22 and Mike | 800

I am not very good with Excel but in English I would imagine the formula would play out something like this:
If Duplicate in Column A then Sum Column B (for cells with duplicate values)

Can this be done with an Excel formula or would VBA be required?
  • +
    0 Votes
    ThumbsUp2

    Excel itself, using just formulas, wouldn't do it for you. Best use a script to do the job.

    You would search through the list of names (column A) storing each 'unique' name in a variable array, then go through that array, one item at a time, searching the original list for a match and adding the contents of column B next to that name to a summation variable. After each loop through, print the name and the contents of the summation variable to another cell/sheet, reset the variables, move to the next unique name in the array and do it all over again. There's a little more to it than that, but you get the drift.

  • +
    0 Votes
    ThumbsUp2

    Excel itself, using just formulas, wouldn't do it for you. Best use a script to do the job.

    You would search through the list of names (column A) storing each 'unique' name in a variable array, then go through that array, one item at a time, searching the original list for a match and adding the contents of column B next to that name to a summation variable. After each loop through, print the name and the contents of the summation variable to another cell/sheet, reset the variables, move to the next unique name in the array and do it all over again. There's a little more to it than that, but you get the drift.