Macro to remove duplicate values based upon 2 criteria

[bearcub]

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

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

 

[DanteAmor]

Try this.
The macro cleans column F, only leaves one value for each "Customer Name" and "Order Item Code" key.
The macro assumes that the data begins in row 2.

Sub remove_duplicate()
  Dim i As Long, dic As Object, cad As String
  Set dic = CreateObject("scripting.dictionary")
  For i = 2 To Range("G" & Rows.Count).End(xlUp).Row
    cad = Range("G" & i).Value & Range("H" & i).Value
    If Not dic.exists(cad) Then
      dic(cad) = Empty
    Else
      Range("F" & i).ClearContents
    End If
  Next
End Sub

 

[bearcub]

Thank you Dante, it works like a charm.

I added another criteria (for column I) because a customer can buy multiple services on the same Order Item Code but the order code would be different.

So, it now looks like this:

cad = Range("G" & i).Value & Range("H" & i).Value & Range("I" & i).Value

Really cool macro - very elegant. What is the "scripting Dictionary" object? Is this a built in or an object that you created on the fly?

Thank you,

Michael

 

[DanteAmor]

Thank you Dante, it works like a charm.
I added another criteria (for column I) because a customer can buy multiple services on the same Order Item Code but the order code would be different.
So, it now looks like this:
cad = Range("G" & i).Value & Range("H" & i).Value & Range("I" & i).Value
Really cool macro - very elegant. What is the "scripting Dictionary" object? Is this a built in or an object that you created on the fly?
Thank you,
Michael

Dictionary is an object already built.

Review this:

Dictionary object

Office VBA reference topic

docs.microsoft.com

And this:

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery