Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-14-2017, 10:02 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Talking Inventory-Trying to add numbers to a total based on the numbers above.

I'm trying to make an inventory spreadsheet that scans for numbers in the spreadsheet and then adds the amount of the said item to a total value in another sheet. There is a warehouse and products can be everywhere, but I do not know exactly which products will be where.

Numbers are written into said spreadsheet as 1001(ID);60(amount). Then underneath that I am pulling the numbers before the semicolon, and after the semicolon. These numbers are below in ID and AMOUNT sections in the spreadsheet. I just need a way to scan for all the numbers and then pull the number below it into a total value that when all the values for that product are pulled, will display the total amount. (Sorry for the convoluted and messy explanation).Keep in mind that the numbers may change and there will be around 800 different items. Sorry, I understand that this may be very easy, but I am very inexperienced when it comes to any sort of VBA code like this requires (or I believe requires)

Add amount here is where I want the totals to be added, but I moved them 1 to the right to show this. The data should be entered correctly and what I want it to do is scan/search for a number, and based on the number, add it to the corresponding total value.

Thanks, Daniel
Attached Files
File Type: xlsm Sample.xlsm (11.1 KB, 3 views)
Reply With Quote
  #2  
Old 07-14-2017, 11:32 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 895
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Maybe like shown in the attached (Sheet SAMPLE).
Attached Files
File Type: xlsm Sample_2.xlsm (12.4 KB, 5 views)
Reply With Quote
  #3  
Old 07-14-2017, 11:49 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

Thank you so much! I really appreciate it!
Reply With Quote
  #4  
Old 07-15-2017, 10:13 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 629
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

A VBA option.
No need for anything on the SAMPLE sheet other than the original ID;AMOUNT range.
The ID list and TOTALS are recalculated and written to columns M:N each time the command button is clicked.
Attached Files
File Type: xlsm Sample_beeker_vba1.xlsm (19.6 KB, 5 views)
Reply With Quote
  #5  
Old 07-17-2017, 07:15 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

Thanks, but some of the numbers are off when I add to the amount. They add but don't seem to be next to their corresponding ID value.

It seems to not work if it detects a space in one of the cells. Is there any way to fix this error?
Reply With Quote
  #6  
Old 07-17-2017, 07:55 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 629
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

You might want to mention what you're eluding to and/or who your response is for.
Reply With Quote
  #7  
Old 07-17-2017, 08:00 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

Sorry NoSparks, what I am eluding to is the 1st sample sheet with the formulas. It seems to error when it detects a space or a box without a semicolon. (I am changing the selection area to fit what I need it for.) I am trying to make it "look" like a warehouse with title boxes that say A1C4 (Aisle 1 Column 4).
Reply With Quote
  #8  
Old 07-17-2017, 08:09 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 629
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

My VBA suggestion eliminates the need of having any formulas on the 1st sample sheet, or anything other than the ID;Amount data, so I guess it isn't my suggestion you're asking about.
Reply With Quote
  #9  
Old 07-17-2017, 08:10 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

I'm sorry. You're correct.

I just am not sure how to edit the VBA to make it to scan the area that I need it to as well as put the value in another cell. (I'm kinda a noob when it comes to VBA.)
Reply With Quote
  #10  
Old 07-17-2017, 08:29 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 629
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

That Aisle part was not indicated in your sample.
To accommodate this in my code change this line
Code:
If arr(i, ii) <> "" Then
to
Code:
If InStr(arr(i, ii), ";") <> 0 Then
What "other cell" are you wanting to put the value(s) into ?
And what does your ID;Amount data really look like ?
Reply With Quote
  #11  
Old 07-17-2017, 08:39 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

Your version makes it so that it gives me the totals for all the numbers in the cell in a list form. I want it so that it give me the total based on the number in a specific cell like xor's function version. (Get input (1001) get total from (1001;60 and 1001;10 and 1800;60) and say the total for 1001 (70)).

Can you edit xor's for me to just ignore anything that doesn't have a semicolon? Again thanks for all your help with this.
Reply With Quote
  #12  
Old 07-17-2017, 09:25 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 629
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

The macro totals the amounts for the unique ID's on the sample sheet and puts them into a sorted list.
My line of thought being simple formulas on your ITEMTOTALS sheet to look at the 2 column list rather than looking at what you are attempting to use.

I admit not understanding what your trying to achieve.

Good luck with your project.
Reply With Quote
  #13  
Old 07-17-2017, 09:36 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

xor can you edit one of the functions to make it so that it looks at only numbers with semicolons?
Reply With Quote
  #14  
Old 07-17-2017, 11:12 AM
beeker223 beeker223 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jul 2017
Posts: 8
beeker223 is on a distinguished road
Default

Thanks, I got it figured out.
Reply With Quote
Reply

Tags
inventory, sum, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Excel to recognize week numbers based on specific dates Ktyler Excel 5 02-06-2017 11:08 AM
Open a document based on a template, the page numbers in the TOC won't update billy8b8 Word 2 10-04-2015 06:06 AM
Search a cell that contains words and numbers and convert the numbers to metric Carchee Excel Programming 36 10-08-2014 03:16 PM
How do I refer to page numbers, when the numbers change as I prepare the document? StevenD Word 5 11-29-2012 12:52 AM
Create index based on bullet numbers EZRider Word 0 07-10-2009 09:21 AM


All times are GMT -7. The time now is 02:16 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft