VBA code to speed up and automate multiple account, multiple site stock movements

[cfo20code]

MVPs

I need help with vba code to speed up and automate multiple account, multiple site stock movements. The current file has formula and due to the large size of the data set(this is growing every single day!), running the report this way is now crushing excel(file attached).
Input Tab : Column A - stock codes, Main Header Row(OS, GRNS, WMS, LGRNS, CS, TRANSFERS) are Stock Movement Categories.
TRANSFERS represent inter site movements eg. WML>CW(WML to CW) and CW>WML(CW to WML).
Sub Header Row(WML,MCO,DW,CW,IH,WH,YEA,OTH,SPW) are Stock Storage sites. This is the raw data tab.



Output Tab: (Desired Result):
Column A: Currently builds the Key ID for Site & Stock Code(Concat Account(column B) & Code(Column C).
Sumifs formulae are then used to sum up stocks by storage sites(from Input Tab) under Headers(OS, GRNS, WMS, LRGNs, CS),
Ifs formulae are used to sum up all intersite transfers).

Please note that all sites share the same 10 000 different stock items(Column A in Input tab and Column C in Output tab). Had to truncate the file size(currently about 30MB). (May be adding a single tab with one set of stock items in Column A and the site movements counts tabulated from there-have hidden the tab incase this will help simplify the solution)

Although this works, as it is run daily, it is too much real estate, is very slow, crushing Excel due to the ever growing size of the data set. I have tried recording a macro on this and it crashes due to formulae(I suspect). Would also be grateful if the vba solution can accept dynamic update of stock randomly across the sites in the OUTPUT sheet(column B -sites are ordered in groups currently). Thank you in advance.

cfo

[p45cal]

I've been able to get similar results to you and get them updating almost instantly (partly due to it being a small subset of your data).
One reason for differences in values in the picture below is that some of your formulae refer to the empty cells below the table on the INPUT sheet.
What version of Excel are you using?
How do you get this data into Excel? Does it come from external files? If so it would very probably be easier (and lighter on Excel) to grab this data directly from those files.
It would help if you could perhaps share a workbook on a file-sharing site which would allow bigger files, then share a link to it (privately if you want).
It would be even better if you could share the source files of this data (if they exist, that is).
2020-12-05_024441.jpg

[Paul_Hossler]

Another approach is to take the INPUT and process it to a pivot table-friendly format

Capture.JPG

Not sure of some of your business rules, and I think some of your test data as formulas were incorrect

Option Explicit

Dim rowOut As Long
Dim wsIn As Worksheet, wsTemp As Worksheet

Sub PrepareForPivotTable()
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Temp").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Worksheets.Add.Name = "Temp"
    Set wsTemp = Worksheets("Temp")
    
    Set wsIn = Worksheets("INPUT")
    
    rowOut = 1
    
    With wsTemp
        .Cells(rowOut, 1).Value = "Code"         '   e.g. RB515
        .Cells(rowOut, 2).Value = "Category"     '   e.g. OS
        .Cells(rowOut, 3).Value = "Account"      '   e.g. WML
        .Cells(rowOut, 4).Value = "Qty"          '   e.g. 180
    End With
        
    rowOut = rowOut + 1
    
    Call pvtList("OS", wsIn.Range("B:J"))
    Call pvtList("GRN", wsIn.Range("K:S"))
    Call pvtList("WMS", wsIn.Range("T:AB"))
    Call pvtList("LGRN  ", wsIn.Range("AC:AK"))
    Call pvtList("CS", wsIn.Range("AL:AT"))
    Call pvtList("TRANSFERS", wsIn.Range("AU:BN"))
    
    wsTemp.Cells(1, 1).CurrentRegion.Name = "Data"
    
    
    Application.ScreenUpdating = True
End Sub




Private Sub pvtList(Cat As String, R As Range)
    Dim r1 As Range
    Dim iRow As Long, iCol As Long
    Dim rowCell As Long, colCell As Long
    Dim v As Variant
    
    Set r1 = Intersect(R, wsIn.UsedRange)
    
    With r1
        For iRow = 3 To .Rows.Count
            For iCol = 1 To .Columns.Count
                If .Cells(iRow, iCol).Value > 0 Then
                    rowCell = .Cells(iRow, iCol).Row
                    colCell = .Cells(iRow, iCol).Column
                    
                    wsTemp.Cells(rowOut, 1).Value = wsIn.Cells(rowCell, 1).Value        '   code
                    If Cat = "TRANSFERS" Then
                        v = Split(wsIn.Cells(2, colCell).Value, ">")
                        wsTemp.Cells(rowOut, 2).Value = "TRANS"                         ' category
                        wsTemp.Cells(rowOut, 3).Value = v(1)                            ' account
                    Else
                        wsTemp.Cells(rowOut, 2).Value = Cat                            ' category
                        wsTemp.Cells(rowOut, 3).Value = wsIn.Cells(2, colCell).Value    ' account
                    End If
                    
                    wsTemp.Cells(rowOut, 4).Value = wsIn.Cells(rowCell, colCell).Value    '   qty
                    
                    rowOut = rowOut + 1
                End If
            
            Next iCol
        Next iRow
    End With
End Sub

[cfo20code]

Thanks P45cal - the source data is a csv download from a bespoke software for stock management. Using Excel 2016. I had to truncate the file(hence empty data bit in some cells). Let me try and set up a link to save the whole file and will get back to you. Much appreciated.
Cfo

[cfo20code]

Hi MPVP Paul - thanks a lot for this. Let me try this out. Also, MVP P45cal has an idea to use the source data so will post a large file of this and share link. I will feedback on the script you sent. Thanks once again.
Cfo

[cfo20code]

Hi here is the link with the full file.

https://drive.google.com/file/d/1KHx...ew?usp=sharing

Thanks

cfo

[Paul_Hossler]

As example --

The attachment includes only the pivot tables (copy/paste values) based on the temp sheet generated by the macro (runs in 3-4 seconds)
Normally INPUT and Temp would be in the the workbook

If you want to try it out, put the macro in your real workbook, run it, and then make pivot table(s)


Capture.JPG


Some data issues

1. Some Codes are blank
2. I think I did the transfer calculations correctly
3. Data goes wahoonie-shaped if I sort INPUT since the OUTPUT formulas don't update and now point to the wrong row.
4. Don't know about KiDm field - the two sub-fields are there already


If you do have a formatting requirement for management, a second macro can take the PT and make a pretty report

[cfo20code]

Hi Paul - data is random test data(stock in KGs that could be between 1 and up to 100k). Sorry for not specifying.
Thanks

[cfo20code]

Hi Paul - thanks. Have you seen the latest file I sent through the link?
https://drive.google.com/file/d/1KHx...ew?usp=sharing
This is a third of the full data set. I would replicate the code soluton for full coverage once done. I will play with what you just sent and feedback. Thanks once again.

[Paul_Hossler]

OK

If there's any confusion, I'll PM you a link to my Google Drive with the more complete file

[cfo20code]

Hi Paul - have replaced the sample input data with the real dataset - script been running for the last 5 mins, and still running. 100% memory usage. Timing it(hopefull wont crash). Thanks

[Paul_Hossler]

Hmmmm - seems long

PM me link to your GD with the full dataset (if you can) and I'll look for bottle necks in my macro

[cfo20code]

hi Paul - the full dataset crashed Excel. It does work on a small data set. Thanks

[Paul_Hossler]

What version of Excel? 32 bit or 64 bit?

PM me link to your GD with the full dataset (if you want) and I'll look for bottle necks in my macro

[cfo20code]

Hi Paul - I have deleted the Output file(Formulae caused the crashing) and replicated your runtime. Can you please add code for Intersite Transfers be part of the Temp file so they can be pivoted by site? eg for CW - it will show WML>CW, DW>CW, SPW>CW, WH>CW example. Alternatively, a separate pivot?
Thanks.

[Paul_Hossler]

I tried to make the PT look like your Output. What changes did you want?

Input

Input.JPG


Your original Output

Output.JPG


My pivot table

Pivot.JPG

[cfo20code]

Hi Paul - have attached the Intersite by Site Transfer pivot idea. And yes, my team would benefit from a Management Report as per your brilliant idea. Will transform the execution of our Working Capital and Sustainability Strategy significantly.

Thanks once again.

[Paul_Hossler]

Capture.JPG

How about this format? Little different, but putting the To and the From in separate fields is better (I think)

Can be done the other way

This PT is built off the same INPUT sheet as the non-transfers (OK?? or do you have a separate sheet for transfers??))

[p45cal]

See attached.
I've checked the values and all are the same as yours except for where I think your formulae go a bit awry; see cell I3085 and below (in your original file).
I've replaced your formulae with plain values because they were taking a while to recalculate.
This is a pivot table based on a Power Query query.
Refreshing takes less than a second (right-click the pivot and choose Refresh).
There's a chance that your version of Power Query might throw an error - it can be tweaked.

It would probably be even more robust if it were to query the csv file directly - and at the same time obviate any manipulations you need to perform to get that data into Excel yourself, and make updating your spreadsheet no more complex than replacing the csv file with a more recent version and refreshing the pivot.

You can PM me here to share a sensitive link to such a csv or for my private email address.

[cfo20code]

Hi P45cal - Thanks for this. Let me review and get back to you. MVP Paul has a similar approach that eliminates need for the OUTPUT file(thats removes the boilerplate formulae. Formulae as you know fails the DRY(DONT REPEAT YOURSELF) ethos in modelling and coding!
Thanks once again. PS: I am a Surrey Alumni(in case you are - would be great to go PM, about to start an AI MSc there!)
Thanks