bearcub
Well-known Member
- Joined
- May 18, 2005
- Messages
- 731
- Office Version
- 365
- 2013
- 2010
- 2007
- Platform
- Windows
I apologize for the lengthy explanation and I hope it isn't confusing.
I am trying to create a report where I can measure all the commissions paid for our company sales. I need to clean the data so there is only 1 sales transaction amount so I can total the commission expense that was paid on that order.
The data dump I get from our commission system has all the information we need to pay our sales department - Sales Order # (order Code), Sales Type (Order Item Code), order amount, order date, employee, etc. There is a line for each employee with the order amount, order Code, order Item code, etc. The thing that is different between each line (besides the employee name) is the commission earned for each transaction.
If I use this data without "cleaning it" in a pivot table, I am going to get inflated numbers that don't make any sense. it will sum the orders by the number of people.
I need to clean the Sales Amount field so it has 1 value for each order. I need to removed duplicate invoice amounts.
Is there a way to tell a pivot table to exclude these multiple values by some sort of calculated field or would I need a macro to clean the data?
I went through this process once to clean the data (it took several hours) where I looked at the Order Code (the Sales order number) and the Order Item Code and scrubbed the data so that their was only 1 value for each combination.
For a macro, I was thinking about having 2 criteria: Order Code (invoice #) and the Order Item Code (the transaction type - new business, renewal, professional Services, multi-year). When combined together, there should only be 1 sales amount per transaction type. I can then find the Cost of commission expense for each order.
An order code could have 1 or more Order Item codes. There will be a dollar value associated with each order. I need to clean the data so that each order code and order Item code only has 1 sales amount even though their might be 14 people being paid on it.
For example:
Customer Name: JP Morgan
Order Code: 0062E00001C57DJ
Order item Code: "NARR-2019-04-23" -
Oder Amount: $17,000
# of people paid: 7
Total Commissions paid: $6,000
Order Code: 0062E00001C57DJ
Order item Code: "Renewal-2019-04-23" - dollar amount is
Oder Amount: $6,000
# of people paid: 7
Total Commissions paid: $1,000
Below is an example of type of report I want to generate.
AdvancedMD Software for $154,080 might have 10 people being paid on it. Each person has that sales amount in their bookings field. My job is to remove 9 of them so their is only amount for $154,080 remaining.
Aflac for $182.015 might be comprised of 6 people, 1 person would have the bookings field populated with $182 015 but the 6 people would have -0-.
This is my challenge
Am I asking to much of Excel?
Michael
I am trying to create a report where I can measure all the commissions paid for our company sales. I need to clean the data so there is only 1 sales transaction amount so I can total the commission expense that was paid on that order.
The data dump I get from our commission system has all the information we need to pay our sales department - Sales Order # (order Code), Sales Type (Order Item Code), order amount, order date, employee, etc. There is a line for each employee with the order amount, order Code, order Item code, etc. The thing that is different between each line (besides the employee name) is the commission earned for each transaction.
If I use this data without "cleaning it" in a pivot table, I am going to get inflated numbers that don't make any sense. it will sum the orders by the number of people.
I need to clean the Sales Amount field so it has 1 value for each order. I need to removed duplicate invoice amounts.
Is there a way to tell a pivot table to exclude these multiple values by some sort of calculated field or would I need a macro to clean the data?
I went through this process once to clean the data (it took several hours) where I looked at the Order Code (the Sales order number) and the Order Item Code and scrubbed the data so that their was only 1 value for each combination.
For a macro, I was thinking about having 2 criteria: Order Code (invoice #) and the Order Item Code (the transaction type - new business, renewal, professional Services, multi-year). When combined together, there should only be 1 sales amount per transaction type. I can then find the Cost of commission expense for each order.
An order code could have 1 or more Order Item codes. There will be a dollar value associated with each order. I need to clean the data so that each order code and order Item code only has 1 sales amount even though their might be 14 people being paid on it.
For example:
Customer Name: JP Morgan
Order Code: 0062E00001C57DJ
Order item Code: "NARR-2019-04-23" -
Oder Amount: $17,000
# of people paid: 7
Total Commissions paid: $6,000
Order Code: 0062E00001C57DJ
Order item Code: "Renewal-2019-04-23" - dollar amount is
Oder Amount: $6,000
# of people paid: 7
Total Commissions paid: $1,000
Below is an example of type of report I want to generate.
AdvancedMD Software for $154,080 might have 10 people being paid on it. Each person has that sales amount in their bookings field. My job is to remove 9 of them so their is only amount for $154,080 remaining.
Aflac for $182.015 might be comprised of 6 people, 1 person would have the bookings field populated with $182 015 but the 6 people would have -0-.
This is my challenge
| By Customer - Region Paid | Bookings | Commissions | % Sales-Comp |
| AdvancedMD Software | $154,080 | $924 | 1% |
| NALA | $154,080 | $924 | 1% |
| Aflac | $182,015 | $8,634 | 5% |
| NALA | $182,015 | $8,634 | 5% |
| Alberta Blue Cross | $208,650 | $63,593 | 30% |
| NALA | $208,650 | $63,593 | 30% |
| Allegis Group | $131,953 | $5,081 | 4% |
| EMEA | $17 | 0% | |
| NALA | $131,953 | $5,064 | 4% |
| Alm. Brand A/S | $226,305 | $45,761 | 20% |
| EMEA | $226,305 | $42,550 | 19% |
| NALA | $3,212 | 0% | |
| Alticor | $229,632 | $11,079 | 5% |
| NALA | $229,632 | $11,079 | 5% |
| American Credit Acceptance | $197,780 | $5,343 | 3% |
| EMEA | $16 | 0% | |
| NALA | $197,780 | $5,327 | 3% |
| AXA Investment Managers | $41,933 | $1,871 | 4% |
| EMEA | $41,933 | $1,837 | 4% |
Am I asking to much of Excel?
Michael
