1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel- Distributing data

Discussion in 'Business Applications' started by MariaPreto, Feb 9, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    :eek:
    Sorry guys another question.
    I'm doing this in a very tiresome way, but I'm wondering if there is
    an easy to do it.
    Here is my problem:

    I have an excel file with 3 columns;
    Names, Quantity, Frequency. Like that;

    Names Quantity Frequency
    Mary 88 Mary
    John 56 Mary
    Paul 23 Mary
    John
    John
    Paul
    I need to create a fourth column, it can be called distribution, with the data
    from Quantity column divided equally by each name from Frequency column.

    For instance, Mary has a quantity record of 88, and Mary is repeated 3 times
    on Frequency column. Therefore 88/3=29.3. At the fourth column "Distribution"
    that I'm going to create the 29.3 is going to appear 3 times.

    John = 56 and appears 2 times. Therefore 56/2 = 28
    At the end my worksheet is going to look like:

    Names Quantity Frequency Distribution
    Mary 88 Mary 29.3
    John 56 Mary 29.3
    Paul 23 Mary 29.3
    John 28
    John 28


    Thank you very much for your time.
     
  2. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    The problem is the fact that John occurs twice in Column A (along with Paul), while Mary occurs three times in Column C. If all the names (the ones without associated quantities) occurred in Column C, it would be easier. It might still require a VBA program, but it's easier. How do you acquire this information and is it possible to reformat it as I has outlined?
     
  3. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    I'm sending the a screen shot of the excel file so you can understand what I need.
    Thank you very much for your time.
     

    Attached Files:

  4. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    I think I've got a good start. My question is, why, since Mary appears 3 times, John 2, Roy 5 and Mike 4 and the number next to each respective name is always the same, can't we have a separate list that just says:
    Mary 29.3
    John 28.0
    Roy 15.6
    Mike 23.25

    That would be relatively easy to do. Repeating the names makes it a little tougher.
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Do the repeated entries in Column A have values associated with them in Column B as well?

    Rollin
     
  6. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    Unfortunately I do need to repeat the names, because I have to plot those numbers from the Distribution columns in a map.
    Right now I'm doing everything mannually, which is very stupid.
    By the way, how did you create the list without repeating the names?
    Thank you very much for your time.
     
  7. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    Yes they do. ;)
     
  8. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Check out the attached spreadsheet. I think this will work. You may have to modify some of the array formulae if your lists are longer than 30 entries, but that's easy. This finds the unique names in Column C and then computes a divisor for the number. Then it does a double lookup - first for the number to be divided and then for the divisor. Now, if Mary occurs more than once in Column A - all bets are off. But this should work for you.
     

    Attached Files:

  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Put the following formula in cell D2 and then copy the formula down for all other cells. I included a repeated entry in column A (Mike) and attached your sample workbook to show you that it works. To copy the formula down to other cells simple place the formula below into the first cell (D2) and then right click the cell and select copy. Then select the entire range of cells below cell D2 and then right click and choose
    PasteSpecial --> Formulas

    =SUMIF(A:A,C2,B:B)/COUNTIF(C:C,C2)

    Rollin
     

    Attached Files:

  10. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Talk about cutting to the chase. That's pure genius. Very elegant solution.
     
  11. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    Dear ddockstader,

    Thank you very much for your solution it was very smart and advanced.
    I appreciate your time.
    Kudos for you ;)
     
  12. MariaPreto

    MariaPreto Thread Starter

    Joined:
    Sep 22, 2004
    Messages:
    13
    Dear Rollin_Again,

    As ddockstader said very clever and elegant solution.
    Thank you very much for helping me out.

    You guys are very smart. ;)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/328673

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice