VBA Sort, Insert Row at each Unique Entry of Specific Column

[bpiereder]

I run a monthly report with the below sample row with many entries. The report is initially sorted by Order#. The Report does have a header in 1:1.

Data Example.xlsx

Sample Data:
Date Order# Client# ClientName ProductCode Quantity ShippingDate AgentName TotalSale$
2/1/2013 ORD-275249 CLI-700009606 Client 1 Item 1 1 2/1/2013 Agent 1 14.99
2/1/2013 ORD-275250 CLI-700008203 Client 2 Item 2 1 2/1/2013 Agent 2 17.99
2/1/2013 ORD-275251 CLI-700001426 Client 3 Item 3 1 2/1/2013 Agent 2 24.99
2/1/2013 ORD-275252 CLI-700004453 Client 4 Item 4 1 2/1/2013 Agent 1 59.99
2/4/2013 ORD-275253 CLI-700002655 Client 5 Item 5 1 2/4/2013 Agent 5 36.99
2/4/2013 ORD-275254 CLI-700007321 Client 6 Item 6 5 2/4/2013 Agent 6 99.94
2/5/2013 ORD-275255 CLI-700004611 Client 7 Item 7 1 2/5/2013 Agent 1 79.99
2/5/2013 ORD-275256 CLI-700009625 Client 8 Item 8 1 2/5/2013 Agent 1 24.99
2/6/2013 ORD-275257 CLI-700005393 Client 9 Item 9 5 2/6/2013 Agent 9 299.95
2/6/2013 ORD-275258 CLI-700001510 Client 10 Item 10 3 2/6/2013 Agent 9 299.96
2/7/2013 ORD-275259 CLI-700008752 Client 11 Item 11 2 2/7/2013 Agent 9 199.99
2/7/2013 ORD-275260 CLI-700005916 Client 12 Item 12 1 2/13/2013 Agent 5 69.99

I need to make a Macro that (1) sorts the whole report by AgentName, (2) Adds a row beneath each unique AgentName, (3) calculates the sum of the TotalSale$ for each AgentName in the empty cell in the new row in column I, (4) calculate 20% of each AgentName's TotalSale$ sum in the cell to the right, (5) calculate the sum of all sales (without adding the sums of the TotalSale$ for each AgentName that we just calculated) in the cell two cells below the bottom AgentName's TotalSale$ sum, (6) calculate 10% of the sum of all sales and divide it by a dynamic number (which would represent the current number of supervisors in charge of the agents, at the moment it would be 3) in the cell to the right of the sum of all sales, (7) calculate 2.5% of the sum of all sales in the cell two cells to the right of the sum of all sales.

so the above Sample Data would turn into this:

Date Order# Client# ClientName ProductCode Quantity ShippingDate AgentName TotalSale$
2/1/2013 ORD-275249 CLI-700009606 Client 1 Item 1 1 2/1/2013 Agent 1 $14.99
2/1/2013 ORD-275252 CLI-700004453 Client 4 Item 4 1 2/1/2013 Agent 1 $59.99
2/5/2013 ORD-275255 CLI-700004611 Client 7 Item 7 1 2/5/2013 Agent 1 $79.99
2/5/2013 ORD-275256 CLI-700009625 Client 8 Item 8 1 2/5/2013 Agent 1 $24.99
$179.96 $35.99
2/1/2013 ORD-275250 CLI-700008203 Client 2 Item 2 1 2/1/2013 Agent 2 $17.99
2/1/2013 ORD-275251 CLI-700001426 Client 3 Item 3 1 2/1/2013 Agent 2 $24.99
$42.98 $8.60
2/4/2013 ORD-275253 CLI-700002655 Client 5 Item 5 1 2/4/2013 Agent 5 $36.99
2/7/2013 ORD-275260 CLI-700005916 Client 12 Item 12 1 2/13/2013 Agent 5 $69.99
$106.98 $21.40
2/4/2013 ORD-275254 CLI-700007321 Client 6 Item 6 5 2/4/2013 Agent 6 $99.94
$99.94 $19.99
2/6/2013 ORD-275257 CLI-700005393 Client 9 Item 9 5 2/6/2013 Agent 9 $299.95
2/6/2013 ORD-275258 CLI-700001510 Client 10 Item 10 3 2/6/2013 Agent 9 $299.96
2/7/2013 ORD-275259 CLI-700008752 Client 11 Item 11 2 2/7/2013 Agent 9 $199.99
$799.90 $159.98

$1,229.76 $40.99 $30.74

I've figured out the first step but that's it (easy i know). but i can't figure out how to do all the rest of it as in making each inserted row dynamic and the sum references dynamic.

Help will be much appreciated!

[mehmetcik]

This is easy.

Work with me?

[mehmetcik]

I have pasted your data into column a

in column b I have put the formula =SEARCH("agent",A1) which tells me where the word agent first appears on a line

[mehmetcik]

in column c I type =SEARCH(" ",A1,B1+6)
in column d I type =MID(A1,B1,C1-B1)
in column e I type =LEN(D1)

so column d now shows the agent name.
I confirm that there are no leading or trailing spaces by checking the string length in column e.

So this is working. I can put all the formulas together in one. [ I don't need to but it makes the macro shorter and faster. ]

[bpiereder]

I'm not sure that I follow... i tried plugging in a row of data in one column and plugged in your formulas in their respective positions but i'm getting #VALUE! errors in c, d, and e.

[mehmetcik]

Ok I will send you the file as I have it at the moment

step through the macro and you will see what is happening.

[mehmetcik]

Ok

This is what I have

[mehmetcik]

I'm sorry I was going to show you how to work through this but then I thought you'd gone.

[bpiereder]

Ok I can see that you've achieved this but the data is in excel format in separate cells:

Data Example.xlsx

[mehmetcik]

Oops

You never said.

This will take me a few mins to rewrite all that. I had just finished based on what you posted.

[mehmetcik]

Please Login or Register  to view this content.