Excel formula - need assistance with challenging formula or table

Computer system generates a spreadsheet and I am trying to pull together metrics with it. I have 2 columns: Code and Item. There are multiple terms in the code column which are separated by a semicolon. Each term needs to be counted separately and then counted per each item. Here is example of the spreadsheet (sorry count input the picture)

Code Item
Juicy;bright;crisp Apple (red) 0057
Red Apple
Juicy;Ripe Banana
Brown; Ripe Banana
Juicy;plump Grape
Bright;Plump Apple





and here is what I need in the end

APPLE
Bright 2
Plump 1
Juicy 1
Crisp 1

Banana
Ripe 2
Juicy 1
Brown 1

Grape
Juicy 1
Plump 1

Any help would be greatly appreciated...

Originally Posted by m masi

APPLE
Bright 2
Plump 1
Juicy 1
Bright 2
Crisp 1

Banana
Ripe 2
Juicy 1
Brown 1

Grape
Juicy 1
Plump 1

I assume this is a typo and you would only want the Bright text listed once but with a count next to it?

This is probably best suited to VBA, in which case I'm going to be of no use to you. It can be done, using several formulae, but it is a bit messy!!! I have assumed that the extra space in A5 is also a typo. Six (!!!) formulae in play, each one in a differently shaded area. The one in column I is an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...