| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I reshuffle some column data based on a coding scheme?
"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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making a new column based on data in an existing column | Excel Discussion (Misc queries) | |||
| Formatting Rows of Data based on Column Data | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| I need to count data in one colum based on data in another column | Excel Worksheet Functions | |||
| Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
Threaded Mode
