[SOLVED]How to sum dynamic range based on customer ID (Duplicates)

[pete_me_a]

Hello Folks,
how do I sum data in a given colum for a dynamic range which is based on customer ID. The example with test data is attached. I asked this question in another thread but sadly i did not follow the forum rules hece this new thread. In the attahed file I have a list of customer transactions which shows sales units and sales value.

A customer can have 1:N rows. I would like to add all sales units and sales value for a given customer and perform a check on the results. ie if sum of all sales units = 0 and sum of all sales value then returm "Exchange". The problem is that i am only able to compare two rows at a time but I would like to sum on the range of customer ID's. The range is dynamic. My code is as follows: As I say it works but not the way I would like it, you should be able to run the code with the attached file
[*

Code: Select all

Dim i As Long
  Dim RwCount As Long
  Dim Customer_ID As Range
  Dim sales_unit_ID As Range
  Dim sales_value_ID
  Dim CustomerID1 As String 'Customer ID value
  Dim CustomerID2 As String 'Customer ID value
  Dim Result As Range   'Output string - actual result
  Dim Sales_Value1 As Double 'sales value
  Dim Sales_Value2 As Double 'Sales value
  Dim Sales_Unit1 As Double 'Sales Unit value
  Dim Sales_Unit2 As Double 'Sales unit Value
  Dim Total_Sales As Double
  Dim total_units As Double
  Set dict = CreateObject("scripting.dictionary")

  'Count Cells using the Customer ID
  RwCount = Cells(Cells.Rows.Count, "A").End(xlUp).Row
 
For i = RwCount To 2 Step -1
   
  'Set columns to be evaluated
      Set Customer_ID = Cells(i, "A") 'Customer ID column
      Set sales_unit_ID = Cells(i, "B") 'Sales unit column
      Set sales_value_ID = Cells(i, "C") 'Sales value column
      Set Result = Cells(i, "D")
     
  'Capture data from the define columns
      CustomerID1 = Customer_ID.Offset(0, 0)
      CustomerID2 = Customer_ID.Offset(-1, 0)
      Sales_Unit1 = sales_unit_ID
      Sales_Unit2 = sales_unit_ID.Offset(-1, 0)
      Sales_Value1 = sales_value_ID
      Sales_Value2 = sales_value_ID.Offset(-1, 0)
             

  If ((Customer_ID.Offset(0, 0) = Customer_ID.Offset(-1, 0)) And ((Sales_Value1 = 0 Or Sales_Value2 = 0 Or Sales_Value1 < 0 Or Sales_Value2 < 0) And (Sales_Unit1 = 0 Or Sales_Unit2 = 0))) Then
          Result.Offset(0, 0) = "Exchange"
          Result.Offset(-1, 0) = "Exchange"
      ElseIf ((CustomerID1 = CustomerID2) And ((Sales_Value1 < 0 Or Sales_Value2 < 0) And (Sales_Unit1 < 0 Or Sales_Unit2 < 0))) Then
          Result.Offset(0, 0) = "Refund"
          Result.Offset(-1, 0) = "Refund"
      Else
          Result.Offset(-1, 0) = "--OK--"
          If Customer_ID.Value = "Exchange/refund" Then Exit Sub
      End If
  Next i

End Sub



[Sisyphus]

Hi,
Let me see, if I got this correctly:
1. Customer ID is in column A
2. Units sold is in colum B
3. The Sales value is in column C

You want to know the total sale to a customer, both by number of units and sale value.
If both totals = 0 you want the result in column D to be "Exchange".
Now there are a lot of other possibilities:
a) Both are <> zero
b) Units = zero / Value <> zero
c) Units <> zero / value = zero
All non-zero values can be either positive or negative. I believe that gives you an extra 5 or 6 possibilities which you have to provide for in your code even if you feel sure that it will never happen. It can happen if some one makes a mistake - intentionally or not - and your worksheet is designed, it seems to me, to catch mistakes. So, it should know what to do when it catches one.

Your request to total sales (units and value) based on the customer ID is no problem, now that this target is specified. Review it to make sure that no add-ons follow when a solution is presented. You say that the customer ID should be found within a "dynamic range". Please describe this range. I prefer not to look for this description in your code because you say the code doesn't work.

There is a huge deficit of action to be taken in case of the various possible findings. Fill it up and you will be on your way to a solution that does what you want.

[pete_me_a]

Hello Sisyphus,

Thank you for reviewing my question, very much appreciated indeed. You are right there are a lot of other posibilities anf in my code all these will go to the else part of the IF statement, ie they will be flagged as ok. Basically this is customer transaction data and we are only interested in customers who bought something and not refunds or exchanges.

Dynamic range, what I mean by dynamic range is that the number/rows for a customer varies, a customer can have 1 to many rows as shown in the exxanple file. I need to be able to sum all rows for the same customer. At the moment I am only able to sum two rows.

I promise not to change the question, my issue is to know how to be able to sum fore more than two rows for a given customer.

I hope this is clear, I look forward to your answer.

Thank you,

Pete

[Sisyphus]

Hello pete_me_a,
My tuppence worth is attached herewith. It looks like it does what you want.
Enums aren't a common sight on this forum. Therefore I will explain the one I used in your code:

Code: Select all

Private Enum Nws            ' WorkSheet parameters
    NwsCustID = 1
    NwsUnits
    NwsValue
    NwsResult
    NwsFirstRow = 2
End Enum


By default, the first value of an enum = 0. I had to specify a value because I want to start counting at 1, but after that the enum counts up with an increment of 1 without my saying so. By default, the last value would therefore have been 5. I wanted it to be 2 and, for that reason, had to assign a value to it specifically. Had I continued the series the next enum would have had the value of 3 - incremented from the previous one.

The name of the macro is ConsolidateTransactions. You can run it on F5 or by calling it from Tools/Macro/Macros/ConsolidateTransactions & Run.

Have fun!

Except for the last value which is a Row, all others are columns, from 1 to 5 or from A to E. If you ever need to change the columns you can assign other values to the same names and that is all the change your code will need. However, if you decide to assign the value 4 to NwsValue, bear in mind that NwsResult would then automatically become 5.

[pete_me_a]

Hello Sisyphus

You are a star, real star!. This is exactly what i wanted. Thank you very much indeed. I have managed to change the colums and I am getting the corrents results. My file has about 70,000 records to it is taking a while but its working as expected. Thank you so much indeed. Its good to learn VBA. The learnign goes on. I am sure another challenge is on the way.

Thank you

Pete

[pete_me_a]

Hi Sisyphus

Your code is magic. Thank you once again. I am just having problems understanding the code, where do you actaully check that the customer I'Ds are the same? I want to understand and learn and not just use it.

Thanks,

Pete

[charles]

Hi,

I looked at the code "Sisyphus" provided and it looks like the "Find" part of the code looks for the "I'D" in Column A.
It finds the first and looks to see if there is another if not the it places the desired result in the column D.
It will continue the find procedure and if it's a multi it set the boolean for "IsMulti " to true.
Hence the result you are looking for.

I hope this helps. I'm sure Sisyphus will provide you with a better explanation.

[Sisyphus]

Hi pete,
No, can't improve on what Charles already said.
Thank you Charles, for keeping the rock in place while I took a nap.

[charles]

Hi,

At least you get to "Nap" I can only sleep.lol

[pete_me_a]

Hi Charles and Sys,

thank you ever so much indeed. I can now happily tackle a similar problem usimg this code as well.
You guys are stars. Your help is much appreciated Sys

regards,

Pete