?
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
Medium Priority
?
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

764 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