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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

861 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

24 Experts available now in Live!

Get 1:1 Help Now