# Solved: Excel- Distributing data

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

Not open for further replies.

Joined:
Sep 22, 2004
Messages:
13

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.

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?

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:

• ###### distribution.jpeg.jpg
File size:
55 KB
Views:
72

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

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

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.

Joined:
Sep 22, 2004
Messages:
13
Yes they do.

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.

File size:
2.5 KB
Views:
6
9. ### 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:

• ###### MariaPreto.zip
File size:
1.9 KB
Views:
14

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

Joined:
Sep 22, 2004
Messages:
13

Thank you very much for your solution it was very smart and advanced.
Kudos for you

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.

As Seen On