klutz
07-16-2009, 01:11 PM
Friends, a tongue twister and brain cell killahh (for me that is)
I have data in Column A rows 3-21 (country name) data in column B rows 3-21 (country warehouse name) and data in column C rows 3-21 (inventory values).
Do a subtotal count on column B for each location change, at the same time do a subtotal sum of what’s in Column C based on what’s in Column B.
Once I have the subtotal sum (column C) I need to find the max value of subtotal sum of (column C).
After I find the subtotal sum MAX value I take that value and multiply it by 10%. If this result is < 1,000 than it should equal to 1,000. If the result is >10,000 than it would equal 10,000. If the result falls in between (1,000 and 10,000) then it would be the MAX Value of the subtotal sum multiplied by 10%.
Then, once I have the subtotal count (column B) I would take that subtotal count,[I] (not the location counts, but the subtotal count. Example: location with same name 3 times I would count it as one) minus the count that is equal to the max value) and multiply that count by 1000 and add it to the result in step 2, giving me the total sum of MAX value result plus the count result.
Sounds complicated. I have to do this manually to about 20 countries each other day and I wanted to know if you guys can help me with a macro shortcut.
Thanx,
I have data in Column A rows 3-21 (country name) data in column B rows 3-21 (country warehouse name) and data in column C rows 3-21 (inventory values).
Do a subtotal count on column B for each location change, at the same time do a subtotal sum of what’s in Column C based on what’s in Column B.
Once I have the subtotal sum (column C) I need to find the max value of subtotal sum of (column C).
After I find the subtotal sum MAX value I take that value and multiply it by 10%. If this result is < 1,000 than it should equal to 1,000. If the result is >10,000 than it would equal 10,000. If the result falls in between (1,000 and 10,000) then it would be the MAX Value of the subtotal sum multiplied by 10%.
Then, once I have the subtotal count (column B) I would take that subtotal count,[I] (not the location counts, but the subtotal count. Example: location with same name 3 times I would count it as one) minus the count that is equal to the max value) and multiply that count by 1000 and add it to the result in step 2, giving me the total sum of MAX value result plus the count result.
Sounds complicated. I have to do this manually to about 20 countries each other day and I wanted to know if you guys can help me with a macro shortcut.
Thanx,