Dynamically create variables based on sheet data

**beeawwb** · 05-01-2007, 01:29 AM

Hi everybody,

I'm currently working on a project and I'm running into some problems with creating variables.

What I've got is 4 "types" of people. Gold, Silver, Bronze, and Red. Each individual person has a limit to how much money they get allocated. So Person1 (Gold) might get 70, but Person2 (Gold) might get 60. I have an available "pool" of money which goes to each group, and I distribute it evenly across the people.

So for example.

The pool has $125 in it. Person 1 gets $1, Person $2 gets $1. Is there still money in the pool? Yes. Can person 1 get any more money? Yes. Give them $1. Can person 2 get any more money? Yes. Give them $1. And so on. When person $1 cannot get any more money, person 2 goes until they cannot get any more money.

This isn't the problem. The problem is, what happens when a 3rd person starts taking from the gold pool?

I don't have much experience with Array functions, and it seems to me like this would be the best way of defining my problem, dynamically creating an array of variables based on a count of "Gold", "Silver" etc from my sheet. Then I see a While function distributing the money (that I can get working ok)

Does anybody have any ideas on what I can do / how to program such an array?

Thanks in advance for any help you can provide, it's greatly appreciated.

-Bob

**Trebor76** · 05-01-2007, 02:29 AM

Hi Bob,

Always glad to try and help a fellow Aussie - I'm in Sydney.

Can you define a specified percentage each group could be allocated, say for instance Gold 40%, Silver 25%, Bronze 20% and Red 15%? If you could, you could then simply divide each group's allocated amount by the number of people within the group.

HTH

Robert

**beeawwb** · 05-01-2007, 02:44 AM

Hi there Trebor, how's the weather over in Sydney?

I can, and do allocate each group based on percentage. To take you through the whole process...

Each group gets allocations based on a sliding scale, which I can adjust at will. Currently, Gold gets twice as much as Silver (Silver*1+100%), Silver twice as much as Bronze, and Bronze twice as much as Red. The percentage is controlled by my main data entry page.

From there, I work out how much each group gets by applying a common denominator based on the number of people.

So with 2 Gold, 3 Silver, 1 Bronze and 1 Red (what I have in the sheet now), you get 2 * 200 = 400 base for gold, 3 * 100 = 300 base for silver, 1 * 50 = 50 base for Bronze, and 1 * 25 = 25 base for Red. Then I say that there is a total of 775 base, and Gold will be allocated 400/775, or approximately 52%.

In a pool of $300, that means approximately $155 goes to the Gold group.

From here, I know that each person will get an average of $77.42, but what happens when A) a person cannot take any more money (Eg, they are limited to $70), and how do I then allocate the extra money to the other person. And B) What about when a Silver person becomes Gold.

The reason for this is each person is capped at how much money they can have, based on their "Level". So, for arguments sake, say Gold has a maximum of $125, and they already have $80. They can only be allocated another $45, and then they are full. Money is then spread among the other Gold until they fill up, and so on.

Thanks again for your help and thoughts, the more I work and think on the problem the more we can work towards a solution.

-Bob

**Trebor76** · 05-01-2007, 03:20 AM

Hi Bob,

Glorious day here in Sydney.

It's hard to provide a solution without seeing the data, but do have something like a category flag (i.e. Gold, Silver, Bronze and Red) assigned to each person. If you do, you could filter the data and make use of some Excel's 11 SUBTOTAL functions.

A similar flag for (say "Y" or "N") could be assigned to those people who are eligible to receive what hasn't been allocated.

HTH

Robert

**beeawwb** · 05-01-2007, 03:33 AM

Happy to post my worksheet in progress so you can see how it all works.

3 sheets.

The "Front End" is "Calculator" and this is where most of the data gets put in by the user.

"Data" is where all my calculations happen to work out averages and percentages and such. That's where the number of claims per level is assigned, as well as any "overflow" to the next level for Gross values.

"Builders" is what I'm working on now. You'll notice each person has an "Outstanding" (That's what they already have) and a "Limit" which is what they can take.

So as stated, the problem is how to allocate from the pool (which would be "Gross claims to all builders" from Data) so that each repairer fills up, and then the leftover gets put into the next pool. So on, etc etc.

The more I'm looking at this problem the more it seems like I'm missing something obvious and it should be really simple. Maybe I'll have a nap when I get home and try with a fresh head.

Thanks again,

Bob

**Trebor76** · 05-01-2007, 07:53 PM

Sorry Bob,

Just not sure about this one. It seems like you may need a fairly detailed nested IF statement incorporating the OR and/or AND functions.

Perhaps the use of an opening and closing balance accounting type methodology may help.

Hopefully another member(s) may have some idea(s).

Good luck,

Robert

**beeawwb** · 05-01-2007, 09:04 PM

Well I was thinking about it last night, and I'm just not sure it can be done outside of VBA. I threw together some code, but it's very limited because it cannot adapt to changing situations.

This is where I'm hoping I can get some assistance making the code adaptable to changing variables and levels.

What I have assumes I have 3 gold builders, in 3 set rows. This will change. Somebody could drop from Gold to Silver, making 2 Gold, somebody could go from Silver to Gold, making 4 Gold. I could have somebody entirely new be added making more than 7 people.

What I'm thinking is this. I always have a count of how many people there are of each type, using a CountIf, defined in my sheet by the name CountGold, CountSilver, etc. If I could use these numbers to define X amount of Variables for Gold, X amount for Silver, and so on... I could adapt my code to suit. The next step would be to find which rows have "Gold" in them, which would allow me to find where to A) get the Limit and B) record the Allocations.

Please Login or Register  to view this content.

Edit: I think I know what needs to be done, I just have no idea how.

If I understand Arrays correctly (And I'm not sure that I do) I could define an Array of CountGold by (for my argument) 3 which would then contain
A) The Row that it is found in
B) The limit
and C) the Allocation

I could then define my code so that For i to CountGold, I add 1 to Array part C, then move to the next Array part C. Once that's done, record Array part C in each Cells(4, Array Part A)

Is that right, or am I nuts?

Any thoughts?

Thanks again,

-Bob

**beeawwb** · 05-01-2007, 10:05 PM

I hate to double post, but I think I'm starting to get my head around a Multi-Dimensional Array, and I'd love if somebody could evaluate my code.

I have 2 main problems.

1) Can't define the array to be from 1 to CountGold, says CountGold must be a constant?

2) Don't know how to properly use Find to fill the Array with Data

Please Login or Register  to view this content.

Thanks again everybody.

-Bob

**beeawwb** · 05-02-2007, 12:28 AM

For anybody interested (i.e. anybody who has had a similar problem) I was able to work it out on my own.

Solutions to my problems were

A) Define the Array with (), and then ReDim the dimensions to a variable

B) Using the Cells.Find to grab an address and then Do/Loop to fill data before moving on to the next Cell. (Modified from the MS Help example)

Please Login or Register  to view this content.

Thanks for reading, it's been fun.

-Bob

Dynamically create variables based on sheet data

LinkBack

Thread Tools

Rate This Thread

Display

Dynamically create array based on sheet data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions