Need alternative excel can't keep a stock balance using sumifs is too slow.

**trickyricky** · 12-05-2018, 05:51 PM

So in a spreadsheet, I have an excel table that lists qty going in and out for many different SKU's and running a sumif minus sumif for the balance takes too long so I need a way to vba code to a button to update.

Jindon helped me with this while back that did this and also included a FIFO cost of goods part to it that was lightning fast but I no longer need the FIFO part and Just want it to run for the Inventory balance and I can't figure out how to seperate it out in the code it's too advanced for me to understand.

Here is an example keep in mind this is a table in excel and actual data entries will be more than 20,000 rows and over 100 different SKU's and will also include more columns for details but this is enough to get an idea.

SKU___________________QTY IN__________QTY OUT__________BALANCE
PRODUCT A_______________5_________________________________5
PRODUCT B_______________5_________________________________5
PRODUCT C_______________5_________________________________5
PRODUCT A________________________________4________________1
PRODUCT B________________________________6________________-1
PRODUCT A_______________10________________________________11

I can't figure out how to attach an actual example file so I hope this is sufficient.

**protonLeah** · 12-05-2018, 05:55 PM

title updated, thanks

**trickyricky** · 12-06-2018, 09:00 AM

Originally Posted by protonLeah

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)

Am I able to receive responses now?

**trickyricky** · 12-09-2018, 01:52 PM

Anyone out there?

**Leith Ross** · 12-09-2018, 08:47 PM

Hello trickyricky,

Without seeing your code or your workbook, it takes more time to provide you with a solution. I created a workbook and macro based on the information you provided.

The macro searches "Sheet1" for the headers: SKU, Qty In, Qty Out. The output is on "Sheet2" in columns "A:B" for the SKU and Balance. The output data is cleared each time the macro runs but not the headers.

This should work quite fast since the inventory balance is placed in a dictionary object and output in an array to the worksheet. You may need to change the worksheet names to match the names in your workbook.

Please Login or Register  to view this content.

**trickyricky** · 12-10-2018, 10:35 AM

Oh, I understand I was just wondering, thanks for your reply. So your code gives me the balance in another sheet but what I'm actually looking for is the running balance per each line calculated in the BALANCE col.

So staying in the same sheet, for the calculations in the BALANCE column is where I need the vba magic.

My current approach is to take the sumif of [qty in] for the lines [sku], minus the sumif of [qty out] for the lines [sku] and those are expanding ranges as the list goes on.

I will attach my example but keep in mind this is a small sample my list will actually get up to 20,000 or more rows with over 100 diff sku's.

**Leith Ross** · 12-10-2018, 10:59 PM

Hello trickyricky,

Thanks for the update. Is this workbook layout identical to the workbook you actually will be using the macro in?

**trickyricky** · 12-10-2018, 11:34 PM

Basically although I will have other columns containing other details such as date, category, cost etc...

**trickyricky** · 12-13-2018, 10:27 AM

I can figure out the other columns later I just need the code for the Balance Column.

**Leith Ross** · 12-14-2018, 01:04 PM

Hello TrickyRicky,

The attached workbook is almost fully functional. There a few features I still need to add. Copy your data into Table1 and test it out. Feel free to move Table2 if you need to but it must stay on the same worksheet as Table1 (for now).

**trickyricky** · 12-16-2018, 08:52 AM

Leith thanks for your time and effort but you are doing too much and its probably because I didn't explain it clearly. I just need vba to do the calculations for the balance column. I know it has to do with using scripting.dictionary and for loops I just dont how but I know its fast.

Ive figured out a way to do it manually and that is to sort the data by sku then simply take the qty in - qty out + the previous rows balance. Then wrapping that in an if statement when the sku changes to start over but it seems to work pretty good.

However i'd still like to see the vba method..

**SjMaxwell** · 12-16-2018, 12:10 PM

Ricky this looks for values in column c starting at row 4and runs a sumif based on the listing name in column C for the qty in then again for the qty out. It subtracts the two then will output the answer in column F. It keeps going until it doesn't find an entry in column C.

Please Login or Register  to view this content.

**trickyricky** · 12-16-2018, 11:20 PM

SjMaxwell I gave your code a shot you're on the right track however it doesn't support for thousands of rows of raw data I am doing. I know it can be done, jindon did it in one of my other threads if anyone wants to disect what he did here: (this was for a table arranged differently with more columns and does FIFO cost of goods sold but i'm just trying to extract the inventory balance aspect of it)

**SjMaxwell** · 12-17-2018, 12:05 AM

I was able to make it output only the inventory balance by deleting these three lines of code:

Please Login or Register  to view this content.

I'm sure without those lines the code is still doing a lot that it doesn't need to, but it's beyond my skill level.

I am curious to see how this gets solved though!

**jindon** · 12-19-2018, 10:33 PM

trickyricky;
re: your message

What's wrong with your formula in Balance column?

**trickyricky** · 12-19-2018, 11:41 PM

Originally Posted by jindon

trickyricky;
re: your message

What's wrong with your formula in Balance column?

So the way I was doing that before you was too slow using the formula sumif of qtyin for the sku minus qtyout and expanding the ranges as it was copied down the column. Then the code you provided for doing the FIFO, you had it do the stocks balance as well, with fantastic speed and efficiancy.

So now I don't need the FIFO part anymore I just want the code you wrote to do the stock's balance perpetually for each line down the list.

**jindon** · 12-20-2018, 12:02 AM

Is this how you wanted?

Please Login or Register  to view this content.

**trickyricky** · 12-20-2018, 06:50 PM

Originally Posted by jindon

Is this how you wanted?

Please Login or Register  to view this content.

jindon you got it! So I just want to know what makes this so fast and how do I learn this is there a youtube video on how you did that?

Need alternative excel can't keep a stock balance using sumifs is too slow.

LinkBack

Thread Tools

Rate This Thread

Display

Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Fast Code for running stock balance of inventory for many different sku's going in and

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Re: Need alternative excel can't keep a stock balance using sumifs is too slow.

Thread Information

Users Browsing this Thread

Similar Threads

Stock/Inventory Control - Running Total

I need help to create an Inventory sheet with a running balance and use a barcode scanner.

[SOLVED] Balance stock of inventory to be debited, while appearing the same code

Create Balance of stock for inventory control

VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

[SOLVED] Formula for Running Balance of stock levels

Inventory Running Balance

Bookmarks

Bookmarks

Posting Permissions