How do I reshuffle some column data based on a coding scheme?

[Motown Mick]

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

 

[squenson via OfficeKB.com]

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]

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]

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.

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]

 

[Motown Mick]

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 wasn’t
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

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.

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

 

[squenson via 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!

 

[Motown Mick]

Dear Stephane:

I tried your quick method of inserting cells in the middle of the column.
It worked for the first worksheet in the workbook I needed to do it to
("wheat & corn"). When I highlighted the elongated column, it said "wheat"
and "corn" in the name box in the upper left--and I inspected some of the
data, and the stuff went in there. I saved the worksheet after that.

But when I inserted the same number of cells into the other worksheets in
the workbook, and copied the new numbers into those columns from the first
worksheet, and tried to highlight the column as I did in the first worksheet,
just a cell reference appeared in the name box.

I tried deleting the new copied numbers, and typing them in manually from
scratch, and the same thing happened. Then I tried typing them in from
scratch, highlighting the new elongated column, and renaming it "wheat" or
"corn", and when I hit enter, it brought me back to the first worksheet.
When I turned back to the second worksheet, I don't think the range got
updated as I planned. I highlighted it, and it still just had a cell
indicator in the name box.

I closed without saving.

What would you suggest I do to update the ranges in the other worksheets?

Mick

 

[squenson via OfficeKB.com]

You can manage the range names by using the menu option Insert > Name >
Define. There you see all your named ranges, and you can manually adapt them,
or even create new ones. Also if you press F5, you can go to a named range --
meaning it will be selected -- and then see its exact extension.

Stephane Quenson

 

[Motown Mick]

Dear Stephane:

F5, for some inexplicable reason, did not detect the ranges established for
the additional worksheets in the workbook "wheat & corn" even though the
analysis had been performed based on those range designations from before I
tried to supplement the ranges. Whenever I was in one of those secondary
worksheets, and clicked on "wheat" or "corn" and clicked "OK", it just
brought be back to the original ranges I had named in the first worksheet.
Even the ranges "boots" and "shoes" that existed in the workbook that I
copied the IF(ISNA formulas from didn't register, although they did in all
the other worksheets in all the other workbooks I tried this in. Funny!

I tried playing with the Insert>Name>define function in the additional
worksheets by defining the longer ranges with the additional numbers in them,
but this seems to have removed these ranges names designations from the first
worksheet. I closed without saving.

Finally, I reasoned that since Excel had somehow recognized the range
designations for the additonal worksheets in the IF(ISNA operation even
though F5 did not show this, it would continue to do so if I typed additional
numbers in their respective columns. My hunch was correct. I checked my
results in the IF(ISNA columns, and it appeared to have incorporated the
product quantities from the new information I typed in the range name
columns.

It all seems strange to me, but I really think that worked.

Thanks for all your help!

Mick

 

[fists1]

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