Suppose I have some data arranged as follows, barring the fact that the
products listings in columns A and C are in reality represented by a numeric
coding system:

A B C D

1 Product 1 Quantity 1 Product 2 Quantity 2


2 Apples 2 Carrots 3

3 Apples 4 Oranges 5

4 Carrots 6 Apples 3

5 Apples 5 Hamburgers 4

6 Hamburgers 2 Gallons of Milk 5


Suppose I wanted to create 2 new columns, E and F, that comprise a
reshuffling and collapsing of the quantity data in columns B and D such that:

I. All of the fruits appear in column E, and all of the vegetables appear in
column F
II. All of the quantities corresponding to a single broad category (fruits
or vegetables) that appear in the same row are aggregated in the same
appropriate column.
III. All of the quantities that correspond to a product that falls outside
of the main fruit or vegetable categories are considered null.

So for instance, the two new columns I would like to create would end up
appearing as follows:

E F

1. Fruits Vegetables


2. 2 3

3. 9

4. 3 6

5. 5

6.

Do you follow? Notice that

Row 2: Everything has stayed exactly the same, because the fruit and
vegetable are already in the proper column they need to be in.

Row 3: Apples in B and oranges in D have been aggregated in E because they
are both fruits, and F, vegetables, is blank because no vegetables appeared
in that row.

Row 4: The apples in column D and the carrots in column B have switched
places because fruits need to be in E, and vegetables need to be in F.

Row 5: Since only the apples in B fall into a relevant product group scheme,
only they are counted. The space corresponding to the hamburgers in D is
blank.

Row 6: It is a complete blank, because neither hamburgers nor milk are
fruits or vegetables.

Can anyone offer advice on how to proceed?

Mick