Distribute Data Evenly Among n Cells

[TimBZKK]

Hello! I have a issue/question that I have been working on for months, to no avail, and I thought I'd get some assistance if possible.

I have a number of rows with 4 columns: Item Name, Model Number, Current Quantity, New Quantity.

I need a formula to add each cell in Current Quantity that corresponds with that cells Model Number, and divide the result as evenly as possible among each of that Model Number, while maintaining a whole number. The number of rows is variable. here is an example.

ITEM NAME	MODEL NUMBER	CURRENT QUANTITY	NEW QUANTITY
football1	AAAAAAAA	7
football2	AAAAAAAA	3
football3	AAAAAAAA	1
baseball1	AAAAAAAB	5
baseball2	AAAAAAAB	12
soccerball1	AAAAAAAC	1
soccerball2	AAAAAAAC	1
soccerball3	AAAAAAAC	5
soccerball4	AAAAAAAC	14

As you can see, there aren't always the same number of Model Numbers. After the function is in place, it should look like this:

ITEM NAME	MODEL NUMBER	CURRENT QUANTITY	NEW QUANTITY
football1	AAAAAAAA	7	4
football2	AAAAAAAA	3	4
football3	AAAAAAAA	1	3
baseball1	AAAAAAAB	5	9
baseball2	AAAAAAAB	12	8
soccerball1	AAAAAAAC	1	6
soccerball2	AAAAAAAC	1	5
soccerball3	AAAAAAAC	5	5
soccerball4	AAAAAAAC	14	5

I am fairly certain that the required formula will have COUNTIF and SUMIF in it, but I'm still lost.

I HOPE some one understands what I am trying to do. If it is still too complicated, let me know, and I will try to explain further.

Thank you in advance for any help you may be able to give!

[dredwolf]

Try this In (I'm assuming this starts at A1?) D2:

Please Login or Register  to view this content.

Extend down

Hope this helps

Note- this one does the same, except in reverse order to what you specified :

Please Login or Register  to view this content.

[TimBZKK]

Sorry for such a late reply, as I was out of my office for the weekend, but thank you! I'm going to try this out now.

[TimBZKK]

IT WORKS LIKE A CHARM!

Thank you, dredwolf! You've helped me solve an issue i've been having for months, and taught me a few new excel functions! Thank you so much!

[dredwolf]

You are welcome !

[akbarmahfuzalam]

Distribute the Number evenly.

I have a sheet- Sheet1 and sheet2
in the sheet1 i have 3 column A B C
wherein column A has text
Column B has number of pages example 200 , 500 1000, 2450 Pages etc...
Column C is blank

what i want is that I want from column B the number of pages need to be divided equally to column C with name which is there in the sheet2 column A2:A100

Item Pages Name
american muslims 110
american ballet theatre 103
american idol 296
american express 103
american assassin 49
american girl 214
amhara 91
american woman 143
american society 129
american revolution 99
amit badana 45
amiga 153
american music awards 121
amherst 148
amok 85

[protonLeah]

akbarmahfuzalam,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.