Solved

using vb code to format/sort/sum data from a basic table in excel sheet with output to another sheet

Posted on 2008-11-01
2
530 Views
Last Modified: 2012-05-05
VB Experts.   Let's say that I want to take the info from 'Table A' below and format/sum the data to look like 'Table B'.    To help with this question, it may be better for you to copy the below data into excel, and delimit using either 'Tab' or 'Space' delimiters..  whatever works to line up the cells in sheet.

Instructions are simple.    I have 'Table A' with groupings of Fruit and their various types.    Each type has a quantity, price and mkt value (quantity * price).    If any type of fruit is of 'Low' quality, then that piece of fruit is always -.10  (let's just say the company incurs a cost of (.10) when fruit is of low quality).     But when any type of fruit is high quality, then it has a certain positive price depending on the fruit type.    

I need to transfer 'Table A' to look more like 'Table B'.    Table B should be sorted by each Fruit_Underlying group's Fruit_Quality (High to Low) and then 'Alphabetically' by the Fruit_Type.   Then take a sum of the Fruit_Value(s) for all the 'High' Fruit_Quality types and place it in new column named Sum_High_Value.    Using the first example from Table B,  $8.80 is the sum of all the 'High' Quality Apple types.    Their corresponding Quantity is 11 for these 'High' quality apples.  

Please help me to process the info from 'Table A' and write it into new 'Table B' line by line, with the correct sum for values and quantities per group, sorted by High-to-Low and alphabetically.

Thanks.


Table A - Unformatted                                    

Fruit_Underlying      Fruit_Type      Fruit_Quantity      Fruit_Price      Fruit_Value      Fruit_Quality      
Apple      McIntosh      2      0.80       $1.60       High      
Apple      Golden      3      0.70       $2.10       High      
Apple      Golden      1      -0.10      -$0.10       Low      
Apple      Granny      5      -0.10      -$0.50       Low      
Apple      RedDelicious      3      -0.10      -$0.30       Low      
Apple      RedDelicious      2      0.90       $1.80       High      
Apple      Golden      2      -0.10      -$0.20       Low      
Apple      RedDelicious      3      0.90       $2.70       High      
Apple      Granny      1      0.60       $0.60       High      
Orange      Navel      2      0.50       $1.00       High      
Orange      Navel      4      -0.10      -$0.40       Low      
Orange      Navel      1      0.50       $0.50       High      
Orange      Valencia      3      0.60       $1.80       High
Orange      Valencia      2      0.60       $1.20       High
Orange      Moro      3      -0.10      -$0.30       Low
Orange      CaraCara      2      -0.10      -$0.20       Low
Orange      CaraCara      1      0.70       $0.70       High
Orange      CaraCara      2      0.70       $1.40       High
Pear      Abate      1      -0.10      -$0.10       Low
Pear      Bartlett      3      0.80       $2.40       High
Pear      Bartlett      2      0.80       $1.60       High
Pear      Seckel      4      1.00       $4.00       High
Pear      Seckel      5      1.00       $5.00       High
Pear      Seckel      3      -0.10      -$0.30       Low
Pear      Seckel      2      1.00       $2.00       High
                         $28.00       

Table B - Formatted                              

Fruit_Underlying      Fruit_Type      Fruit_Quantity      Fruit_Price      Fruit_Value      Fruit_Quality      Sum_High_Value      Sum_High_Quantity      Combined Value      Sum_Low_Value      Sum_Low_Quantity
Apple      Golden      3      0.70       $2.10       High                              
Apple      Granny      1      0.60       $0.60       High                              
Apple      McIntosh      2      0.80       $1.60       High                              
Apple      RedDelicious      2      0.90       $1.80       High                              
Apple      RedDelicious      3      0.90       $2.70       High       $8.80       11                  
Apple      Golden      1      -0.10      -$0.10       Low                              
Apple      Golden      4      -0.10      -$0.40       Low                              
Apple      Granny      5      -0.10      -$0.50       Low                              
Apple      RedDelicious      3      -0.10      -$0.30       Low                   $7.50       -$1.30       13
Orange      CaraCara      1      0.70       $0.70       High                              
Orange      CaraCara      2      0.70       $1.40       High                              
Orange      Navel      2      0.50       $1.00       High                              
Orange      Navel      1      0.50       $0.50       High                              
Orange      Valencia      3      0.60       $1.80       High                              
Orange      Valencia      2      0.60       $1.20       High       $6.60       11                  
Orange      CaraCara      2      -0.10      -$0.20       Low                              
Orange      Moro      3      -0.10      -$0.30       Low                              
Orange      Navel      4      -0.10      -$0.40       Low                   $5.70       -$0.90       9
Pear      Bartlett      3      0.80       $2.40       High                              
Pear      Bartlett      2      0.80       $1.60       High                              
Pear      Seckel      4      1.00       $4.00       High                              
Pear      Seckel      5      1.00       $5.00       High                              
Pear      Seckel      2      1.00       $2.00       High       $15.00       16                  
Pear      Abate      1      -0.10      -$0.10       Low                              
Pear      Seckel      3      -0.10      -$0.30       Low                   $14.60       -$0.40       4
0
Comment
Question by:lblinc
2 Comments
 

Author Comment

by:lblinc
ID: 22859381
Everyone, the 'space' delimeter works better than 'tab'.    However, for easier usage..  I WILL ATTACH THE SHEET FOR THIS SIMPLE EXAMPLE..

See attached .xls.       Thx!


TestProgram.xls
0
 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 500 total points
ID: 22860512
This can be done without VB. See attached file. Sheet2 will currently keep track of all summaries up to 500 rows. To cover more rows just increase the ranges in the array formulas.

Curt
Apples-and-Pears.xls
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now