Google-sheets – Identifying and working with any cells that match

google sheetsworksheet-function

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:

enter image description here

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?

Best Answer

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.

Related Topic