How to take last data entry of previous product and substract from current product?

[Fractalis]

Hello to all,


I have the below macros IncomingProducts and RunAgain.


- IncomingProducts: This macro simulates the input coming from users of StoreA and StoreB. For example purposes I've show the data entries from both stores with arrays.
- RunAgain: This macro only calls IncomingProducts to check if there is new products.


The 3 arrays are only an artificial way to reproduce the values of real code, so the solution doesn't need to do any array operation, because is supposed that we don't
know what is the next value or product that will be introduced for both stores.


As you can see, the array Products contains repeated products, my issue is how to print the sum of each product when they come repeated. I mean, when data for "Shoes" is
introduced more than once print only the last of them and next line to print would be the sum of current product less the sum of previous product. For example:


Supposed the agent entered 3 times in a row for "Shoes": 7, 14, 24 then print 24 (for StoreA) and 2, 4, 9 then print 9 (for StoreB)
The next data entered by agent is for "Jeans" 50 (for StoreA) and 22 (for StoreB) and since there is only one row for Jeans, then that is the last one. Then print in next line


50 - 24 = 26 (for StoreA) and 22-9 (for StoreB)




Then the output I'm looking for is like this:

[FONT=courier new]# | FromStore_A | FromStoreB | Product[/FONT]
[FONT=courier new]1 | 24          | 9          | Shoes[/FONT]
[FONT=courier new]2 | 26          | 13         | Jeans[/FONT]
[FONT=courier new]3 | 149         | 69         | Watches[/FONT]
[FONT=courier new]4 | 593         | 262        | Sweaters[/FONT]
[FONT=courier new]5 | 798         | 360        | Shoes[/FONT]
[FONT=courier new]6 | 1586        | 708        | Jeans[/FONT]
[FONT=courier new]No more products...[/FONT]
[FONT=courier new]
[/FONT]

and the current output I have is this:

[FONT=courier new]#  | FromStore_A | FromStoreB | Product[/FONT]
[FONT=courier new]1  | 7           | 2          | Shoes[/FONT]
[FONT=courier new]2  | 14          | 4          | Shoes[/FONT]
[FONT=courier new]3  | 24          | 9          | Shoes[/FONT]
[FONT=courier new]4  | 50          | 22         | Jeans[/FONT]
[FONT=courier new]5  | 97          | 45         | Watches[/FONT]
[FONT=courier new]6  | 199         | 91         | Watches[/FONT]
[FONT=courier new]7  | 400         | 179        | Sweaters[/FONT]
[FONT=courier new]8  | 792         | 353        | Sweaters[/FONT]
[FONT=courier new]9  | 1590        | 713        | Shoes[/FONT]
[FONT=courier new]10 | 3176        | 1421       | Jeans[/FONT]
[FONT=courier new]No more products...[/FONT]

How can be done this in order to take last entry for each product and substract that value of previous product from value of current product?


I hope make sense. Thanks for any help.


This is the code I have so far. The line commented is where it would go the code for which I need help. Thanks in advance.

Dim i As Integer
Sub Begin()
    Debug.Print "# | FromStore_A | FromStoreB | Product"
    Call RunAgain
End Sub


Sub IncomingProducts()
Dim line As String
FromStore_A = Array(7, 14, 24, 50, 97, 199, 400, 792, 1590, 3176)
FromStore_B = Array(2, 4, 9, 22, 45, 91, 179, 353, 713, 1421)
Products = Array("Shoes", "Shoes", "Shoes", "Jeans", "Watches", "Watches", "Sweaters", "Sweaters", "Shoes", "Jeans")


	//Some code before print
	
    line = i + 1 & " | " & FromStore_A(i) & " | " & FromStore_B(i) & " | " + Products(i)
    Debug.Print line
    i = i + 1
            
    Call RunAgain
End Sub


Sub RunAgain()
    If i < 10 Then
        Application.OnTime Now + TimeValue("00:00:01"), "IncomingProducts"
    Else
        Debug.Print "No more products..."
        Exit Sub
    End If
End Sub

 

[Eric W]

Given the way you set up your simulation, then this would work:

Dim i As Long
Dim LastProduct As String
Dim LastCount As Variant
Dim LastProdCount As Variant
Dim ctr As Long
Sub Begin()
    LastProduct = ""
    LastCount = Array(0, 0)
    LastProdCount = Array(0, 0)
    ctr = 0
    Debug.Print "# | FromStore_A | FromStoreB | Product"
    Call RunAgain
End Sub




Sub IncomingProducts()
Dim line As String
fromstore_A = Array(7, 14, 24, 50, 97, 199, 400, 792, 1590, 3176)
fromstore_B = Array(2, 4, 9, 22, 45, 91, 179, 353, 713, 1421)
Products = Array("Shoes", "Shoes", "Shoes", "Jeans", "Watches", "Watches", "Sweaters", "Sweaters", "Shoes", "Jeans")


'
'    //Some code before print
    If LastProduct = "" Then LastProduct = Products(i)
    On Error Resume Next
    If Products(i) <> LastProduct Or i > UBound(Products) Then
        ctr = ctr + 1
        line = ctr & " | " & LastCount(0) - LastProdCount(0) & " | " & LastCount(1) - LastProdCount(1) & " | " + LastProduct
        LastProdCount = LastCount
        Debug.Print line
    End If
    
    LastCount(0) = fromstore_A(i)
    LastCount(1) = fromstore_B(i)
    LastProduct = Products(i)
        
    i = i + 1
            
    Call RunAgain
End Sub




Sub RunAgain()
    If i < 11 Then
        Application.OnTime Now + TimeValue("00:00:01"), "IncomingProducts"
    Else
        Debug.Print "No more products..."
        Exit Sub
    End If
End Sub

 

[Fractalis]

Hello Eric,

Thanks so much for your help and solution provided. It seems to work pretty fine. The only question I have is if there is a way to avoid including the condition below

or i > UBound(Products)

Since this implies we previously know the number of data entries (length of array products), but actually is not known. Is like we have an input form for the agent to enter the data for both stores but sometimes there are 10 entries, sometimes 5, 20 or 50. Is like Products array didn't exist, so we couldn't use Ubound(Products). I hope make sense.

Thanks again

 

[Eric W]

That line just goes with your testing methodology. Without it, you'd never see the last line of data. Replace it with whatever test you have to determine there's no more data. If you really don't have a way to do that, you could write another macro with just these lines:

    ctr = ctr + 1
    line = ctr & " | " & LastCount(0) - LastProdCount(0) & " | " & LastCount(1) - LastProdCount(1) & " | " + LastProduct
    Debug.Print line

    LastProduct = ""
    LastCount = Array(0, 0)
    LastProdCount = Array(0, 0)
    ctr = 0

and run it manually when you're sure you've got all the data.

 

[Fractalis]

That line just goes with your testing methodology. Without it, you'd never see the last line of data. Replace it with whatever test you have to determine there's no more data. If you really don't have a way to do that, you could write another macro with just these lines:

    ctr = ctr + 1
    line = ctr & " | " & LastCount(0) - LastProdCount(0) & " | " & LastCount(1) - LastProdCount(1) & " | " + LastProduct
    Debug.Print line

    LastProduct = ""
    LastCount = Array(0, 0)
    LastProdCount = Array(0, 0)
    ctr = 0

and run it manually when you're sure you've got all the data.

I understand what you said. I'll see the options to check last line of data or run the other macro like you said. Thanks so much again for the help.

 

[Eric W]

Glad to help!