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
542 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

860 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