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

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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:


Code:
[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:


Code:
[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.




Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624
Given the way you set up your simulation, then this would work:

Code:
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

Active Member
Joined
Oct 11, 2011
Messages
252
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

Code:
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
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624
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:

Code:
    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

Active Member
Joined
Oct 11, 2011
Messages
252
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:

Code:
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,819
Members
414,179
Latest member
anthelsene

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top