+ Reply to Thread
Results 1 to 11 of 11

collating information

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Cardiff
    MS-Off Ver
    2003
    Posts
    123

    collating information

    If you have a spreadsheet where column A is a list of names, for each name there are several data types (listed in column B) some of the data types are repeated. Column C contains the percentage for each of the data tpes for the whole thing. There are 300+ names, each with several data types and most where at least one data type is repeated. How can i easily calculate the total percentage for each data type, for each name.

    eg.
    Please Login or Register  to view this content.
    At present I have been creating a sum function for each data type and then deleting the rows that I don't need to neaten it up. But this is very time consuming and like I said I have to do this over 300 times.

    Any help on this would be great,
    Cheers
    B2B
    Last edited by Back2Basics; 12-05-2008 at 06:18 AM. Reason: Added Code Tags and aligned data

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi and welcome to the board.
    Maybe a Pivot Table might be of help

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Cardiff
    MS-Off Ver
    2003
    Posts
    123
    Hi Arthur,

    Thanks for the advice, but I'm not that hot on excel tbh - hence having trouble. What exactly is a pivot table and how would I go about using it correctly?

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Example attached
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Which XL version do you have?

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    You can start here for an overall explanation of Pivot Tables

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Cardiff
    MS-Off Ver
    2003
    Posts
    123
    This does look like the thing that i need, thanks very much arthur.

    But how did you create the pivot table, a link to how to use pivot tables or a little step by step of what you just did would be amazing...

  8. #8
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    I posted a link on the message above yours. There is also detailed info at contexture
    If you data is sorted, Data - Subtotal might also be a way to go. Just follow the wizard

  9. #9
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Cardiff
    MS-Off Ver
    2003
    Posts
    123
    Thanks Arthur, I'm using office 2003

    Cheers again

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Here's the first formula for one client, adjust from here for other clients/sites:
    Please Login or Register  to view this content.
    EDIT:
    Arthur's pivottable suggestion is way better in the long run. My formula answers the first question but not as flexible as pivottables.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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