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

First, you should create two lists, one with all fruits and one with all
vegetable. Call the ranges "fruits" and "vegetable".

Then, copy the following formula in E2:
=IF(ISNA(MATCH(A2,fruits,0)),0,B2) + IF(ISNA(MATCH(C2,fruits,0)),0,D2)
and in F2:
=IF(ISNA(MATCH(A2,vegetable,0)),0,B2) + IF(ISNA(MATCH(C2,vegetable,0)),0,D2)

Finally, if you do not like to see 0's, then format these cells with #,###,
###,###

Motown Mick wrote:
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

--
Message posted via http://www.officekb.com

Dear Squenson,

Thanks for your reply. It's great to hear from you!

I think I did not create the fruits and vegetable lists correctly.

I applied those formulas you gave me, and Excel simply aggregated what was
in columns B and D, and placed identical results in both E and F. So in E2
and F2, I got 5 in both; in E3 and F3 I got 9 in both, and so on.

To create a list, I simply assigned a unique number to each product, put the
appropriate numbers in a column with the either the header "fruits" or
"vegetable", depending on whether that number represented a fruit or
vegetable, and changed the data in columns A and C to the appropriate numeric
code corresponding to that food. I put those lists I created in columns H
and J.

What do you think I did wrong?

Mick

"squenson via OfficeKB.com" wrote:

First, you should create two lists, one with all fruits and one with all
vegetable. Call the ranges "fruits" and "vegetable".

Then, copy the following formula in E2:
=IF(ISNA(MATCH(A2,fruits,0)),0,B2) + IF(ISNA(MATCH(C2,fruits,0)),0,D2)
and in F2:
=IF(ISNA(MATCH(A2,vegetable,0)),0,B2) + IF(ISNA(MATCH(C2,vegetable,0)),0,D2)

Finally, if you do not like to see 0's, then format these cells with #,###,
###,###

Motown Mick wrote:
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

--
Message posted via http://www.officekb.com

Your approach looks correct. Please check:
1) Have you given a name to the range (not only a header in cell I1 and J1)?
Select the whole column I and type in the text box on the left of the formula
bar, where I1 is written, the text fruit. Do the same for the column J with
the text vegetable.
2) Check that you have not copied twice the same formula in E2 and F2: one
must have the range "fruits" and the other one the range "vegetable".
3) Make sure that a product code appears only in one column, either fruit or
vegetable.
45) If you highlight in one of the formula the part MATCH(B2,fruits,0) and
press F9, what do you get? (You should get N/A if it is not a fruit, or a
number corresponding to the row in the range fruits).
5) Remove all confidential info from the file and post it at
http://www.mediafire.com (free and no registration required). Then paste the
link here so I can access it and review the content.

Stephane Quenson.

Motown Mick wrote:
Dear Squenson,

Thanks for your reply. It's great to hear from you!

I think I did not create the fruits and vegetable lists correctly.

I applied those formulas you gave me, and Excel simply aggregated what was
in columns B and D, and placed identical results in both E and F. So in E2
and F2, I got 5 in both; in E3 and F3 I got 9 in both, and so on.

To create a list, I simply assigned a unique number to each product, put the
appropriate numbers in a column with the either the header "fruits" or
"vegetable", depending on whether that number represented a fruit or
vegetable, and changed the data in columns A and C to the appropriate numeric
code corresponding to that food. I put those lists I created in columns H
and J.

What do you think I did wrong?

Mick

First, you should create two lists, one with all fruits and one with all
vegetable. Call the ranges "fruits" and "vegetable".
[quoted text clipped - 75 lines]

Mick

--
Message posted via http://www.officekb.com

Dear Stephane:

Thanks, I was able to create a list, and name the range in the Name box by
referring to the built-in help in Excel. I realized I did something wrong,
and that you must have been referring to some Excel commands I wasnt
familiar with (I have one more question relating to this name list range
creation€”see below, end of message).

After I did that, it worked just as I planned. Thanks!

As you can probably well imagine, the actual data sets I needed to use this
for were much larger and more complicated than that simple €œfruits &
vegetables€� test/experiment example I posted to you. In reality, rather than
two product & quantity pairs columns, I had four to contend with. But I
grasped the iterative procedure you outlined to me with the plus signs
separating each IF(ISNA(MATCH command corresponding to the product ID &
quantity column pairs, and simply wrote the sum of 4 of these in each results
column instead of 2.

I did this for a whole bunch of workbooks I had that had pairs like this;
shoes & boots, beef & mutton, cigars & pipe tobacco, corn meal & wheat flour,
etc.

I was even able to figure out how to do it for one workbook I had that was a
trio of goods: coats & pants & vests. By just naming a 3rd range €œvests€� and
typing the same formula in a third results column with €œvests€� in the
parentheses in place of coats or pants, and dragging the formula down, I got
the expected results.

It all seems to have worked magnificently as planned. One interesting thing
I noticed is that because my workbooks are all in the same data/column
format, I was able to copy the formula from one workbook to another without
changing the name of the range in the formula. In other words, after I
applied the formula to the first real workbook I needed it for, €œboots &
shoes€�, and gave names to the product ranges list in the next workbook, like
€œbeef & mutton€�, when I copied the IF(ISNA(MATCH formula I had used for
€œboots & shoes€�, it worked fine; all the beef products and mutton products
ended up in their appropriate places. Apparently, column range designations
implicit in names ranges commands in Excel trump the actual words you use.
So that saved me some typing and made the whole thing a lot easier!

The question I alluded to at the beginning of the message is as follows:
after I have completed an analysis on a worksheet, and dragged the
IF(ISNA(MATCH formula down the columns as far as I have data, are there any
special commands I need to use in order to add more products to the lists I
have named? In other words, if I simply type in more numbers corresponding
to goods down the columns in the products list, and highlight the new longer
ranges, and assign the same name to the lists by typing it in the Name box as
I did before, will Excel recognize this expansion of the list, and
incorporate these additional goods in the results of my IF(ISNA(MATCH
columns?

Thanks again for all your help.

Mick


"squenson via OfficeKB.com" wrote:

Your approach looks correct. Please check:
1) Have you given a name to the range (not only a header in cell I1 and J1)?
Select the whole column I and type in the text box on the left of the formula
bar, where I1 is written, the text fruit. Do the same for the column J with
the text vegetable.
2) Check that you have not copied twice the same formula in E2 and F2: one
must have the range "fruits" and the other one the range "vegetable".
3) Make sure that a product code appears only in one column, either fruit or
vegetable.
45) If you highlight in one of the formula the part MATCH(B2,fruits,0) and
press F9, what do you get? (You should get N/A if it is not a fruit, or a
number corresponding to the row in the range fruits).
5) Remove all confidential info from the file and post it at
http://www.mediafire.com (free and no registration required). Then paste the
link here so I can access it and review the content.

Stephane Quenson.

Motown Mick wrote:
Dear Squenson,

Thanks for your reply. It's great to hear from you!

I think I did not create the fruits and vegetable lists correctly.

I applied those formulas you gave me, and Excel simply aggregated what was
in columns B and D, and placed identical results in both E and F. So in E2
and F2, I got 5 in both; in E3 and F3 I got 9 in both, and so on.

To create a list, I simply assigned a unique number to each product, put the
appropriate numbers in a column with the either the header "fruits" or
"vegetable", depending on whether that number represented a fruit or
vegetable, and changed the data in columns A and C to the appropriate numeric
code corresponding to that food. I put those lists I created in columns H
and J.

What do you think I did wrong?

Mick

First, you should create two lists, one with all fruits and one with all
vegetable. Call the ranges "fruits" and "vegetable".
[quoted text clipped - 75 lines]

Mick

--
Message posted via http://www.officekb.com

Dear Mick,

If you expand your range so it contains all the values, then Excel will
consider them in the MATCH formula, nothing else to do. And to avoid changing
the range address each time you insert new items in a list, insert blank
cells *before* the last row of your range and its address will adapt
automatically!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200708/1

"Motown Mick" wrote:

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