Identifying and working with any cells that match

Question

I am trying to find a function, or a series of functions, that allow me to:

(1) Identify if any cell in column A matches any other cell in column A,
(2) add numbers in Column B that occupy the same rows as the matches in column A, and
(3) merge matches rows or delete duplicate rows.

This example will work to explain what I'm trying to get:

I would like to do this WITHOUT having to assume Apples, Oranges, and Bananas are my only fruits. For instances, if two Grapefruit were to appear, I'd like the function to work automatically without having to write a different formula to include Grapefruit.

Is this possible or if not, what are some workarounds?

Answer 1

I'd suggest a pivot table and for this it may be a good idea to label your Original Table (say Fruit in A4 and Qty in B4) though not essential. Then select from A4 to as far down in B as you are likely to require (but can be changed later if still insufficient) and Data, Pivot table report..., click on - Add field next to Rows and select Fruit, click on - Add field next to Values and select Qty.

You can copy the resulting table back in to the original sheet if you wish.

Answer 2

Sorry I took some time to build the spreadsheet.

If you don't know too well how to handle pivot table reports, this spreadsheet I created may help you learn and like to use pivot table reports:

Google Spreadsheets: Identifying and Working with Any Cells that Match