Thread: How to consolidate multiple like entries?

Hi. This has surely been answered hundreds of times before, but I don´t know what terms to use to search for the answer to my question. Here is my dilemma:

I have a list of data with multiple like entries that I need to collate into a simplified list. Example:

CATEGORY___COUNT
=========___======
apple__________3__
red apple_______2__
pear___________9__
Bartlet pear_____1__
Fuji apple______16__
pineapple_______4__
kiwi___________7__
guava_________4__

This list should ideally be consolidated as such:
CATEGORY____COUNT
==========___======
apple_________21__
pear__________10__
tropical fruit____15__

I understand that "pineapple" requires special attention, but I don´t even know how to do the simplest circumstance either (without "pineapple" text string confusion).

Thanks for any help!

Use a pivot table! Pivot tables are designed to do more or less exactly what you've asked for.

I can't upload an example, so please follow these steps (before dismissing the suggestion)
Select a cell in your range
Data -> pivot table & pivot chart report ->
Click through (defaults will be fine)
A pivot table pops up (blue boxes everywhere)
From the little window, drag "CATEGORY" to the "row fields" area - this should make a list of fruit appear in that column
Now drag "COUNT" to the data area
This should effectively replicate your table
Now...
Select each apple cell - like:
click on "apple"
ctrl+click on "Fuji apple"
ctrl+click on "red apple"
right click on red apple
group and show detail
group
You can rename "Group 1" by simply overwriting in the relevant cell
follow similar steps to group pears (select cells in the "ungrouped" column)
You shuold now have a table that looks like

Code:

CATEGORY2	CATEGORY	Total
Apples	apple	3
	Fuji apple	16
	red apple	2
Pears	Bartlet pear	1
	pear	9
Tropical	guava	4
	kiwi	7
	pineapple	4

Now... double-clicking on "Apples" or "Pears" will group/ungroup those groups - JAZZY!

CC