+ Reply to Thread
Results 1 to 9 of 9

How do you create a formula that will sum like values, list unique values and combine

  1. #1
    Registered User
    Join Date
    12-29-2008
    Location
    Winston Salem
    MS-Off Ver
    Office 03
    Posts
    8

    Question How do you create a formula that will sum like values, list unique values and combine

    Good Morning,

    I would say i am somewhat proficient in Excel. I want to create a report but am clueless of how to put a formula together for what i need so i hope the Excel Lords here can help. Below is a sample of the data set i will be using. What i want to do is have excel look in column A for like data, when it finds it look in column B for the unique data sets that are found and list them along with a sum of column C. Basically this: have excel look at what line its on (Column A), group what families are on that line and the quantity of each family for each line. IE: Line K
    Apple-25
    Orange-24
    Grape-24
    Thanks in advance for all your help.

    Line Family Order_Num QTY
    K_ apple 567849405 10
    K_ apple 567848175 15
    K_ orange 574545913 24
    K_ grape 574546028 24
    7L lemon 583482538 1
    7L banana 579432711 1
    7L banana 587487475 1
    7L banana 580694697 1
    7L apple 583356435 19
    7L apple 585199015 6
    H_ grape 587017413 1
    J_ apple 585261773 1
    J_ orange 585171956 1
    G_ grape 584498376 48
    I_ lemon 582486415 2
    I_ banana 583129675 1
    L_ lemon 585685831 1
    L_ banana 585685963 1
    L_ orange 585686631 1
    P_ lemon 584497634 48
    P_ banana 584498129 48
    P_ orange 584498194 48
    J_ lemon 585597788 24
    J_ banana 585597762 24
    J_ orange 586336376 1
    J_ apple 586201166 10
    7L apple 579462130 16
    7L apple 579697347 1
    7L orange 578792172 3
    O_ orange 586734257 1
    O_ apple 586112025 1
    O_ grape 586457560 1
    O_ banana 586516696 1
    O_ banana 587189162 1
    H_ banana 585280864 1
    H_ banana 585348125 1
    H_ apple 585184462 1
    7L apple 587331905 1
    J_ apple 579539267 16
    J_ grape 579535182 16
    J_ grape 579535307 16
    J_ orange 579538947 16
    7L orange 577965027 1
    C_ apple 584005015 20
    7L banana 584613578 16
    7L banana 586424347 32
    Last edited by mikeydread; 12-30-2008 at 08:58 AM. Reason: not following the rules

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Please read the rules

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


    To answer your question maybe PIVOT TABLES is the way to go?

    http://peltiertech.com/Excel/Pivots/pivottables.htm
    Attached Files Attached Files
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    12-29-2008
    Location
    Winston Salem
    MS-Off Ver
    Office 03
    Posts
    8
    thanks for bringing the rules to my attention. i have made the changes to be in compliance. as far as a pivot table, i thought and tried that first, however how can you sum the qty totals in the last column in a pivot?

  4. #4
    Registered User
    Join Date
    12-29-2008
    Location
    Winston Salem
    MS-Off Ver
    Office 03
    Posts
    8
    Ok, i should have looked at the example posted before commenting. Will this pivot work by not combining the line with the family, and then be able to sum the totals of fruit for each line. my end goal would be once i make the report. i want to be able to just drop in the raw data and have the formulas do the work. i know by doing a pivot i can drop the new data in and refresh the pivot however the text to columns and slice and dice is something i would like to avoid.

    thanks

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Thanks for changing your title to something more appropiate, have I got the columns wrong in the pivot?

    Could you post a sample of your spreadsheet

  6. #6
    Registered User
    Join Date
    12-29-2008
    Location
    Winston Salem
    MS-Off Ver
    Office 03
    Posts
    8
    Ok here is the workbook. On the first tab is the report. the second tab is the raw data. i am trying to get excel to look for the line column on data tab, list it in report tab along with what family is assigned to that line along with the quantity of each family.

    thanks again for your help.
    Last edited by mikeydread; 12-30-2008 at 11:55 AM.

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Is this what you need?
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    I've got an alternative for your fruit based example, (attached).
    A quick glance seems to indicate that the same idea will work for your "proper" data, but I haven't looked in detail.

    It is not as neat and tidy as pivot tables, but it should fairly automaticaly adjust as you alter the product list.

    For a finished workbook you whould obviously hide columns F, G & H (or perhaps have them in a seperate sheet).

    Mark.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-29-2008
    Location
    Winston Salem
    MS-Off Ver
    Office 03
    Posts
    8
    thanks for the responses. a quick glance at the worksheets and its not what i am loking for. i will spend more time and be more descriptive for what i am needing. again, thanks for all your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1