Parsing List Of Inventory Items Into Names And Numbers
Mar 28, 2014
I've got a long list of inventory items. The naming convention of these items is not totally consistent. Below is a representative sample of all the different kinds of names:
1800Brandacom (CLIENTA) 25.00
1800Brandacom 25.00
brandb (Variable)
BrandC-100.00-PL-ALG
brandd variable
[Code] .....
I need to separate out the name of the brand and the number (or if the item says "variable", use a 1 for the number) into two columns, so that the results would be:
1800Brandacom
1800Brandacom
brandb
BrandC
brandd
[Code] ....
and:
25.00
25.00
1.00
100.00
1.00
[Code] ....
For the numbers, I have used this array formula to search for the first letter in the string, and then search for the first number following the first letter, and then return all numbers up to "." Unfortunately this does not work consistently when there is a number in the middle of the brand name or the item has no number.
[Code] ....
For the names I have used this formula, which fails when the name has a "-" in it.
[Code] .....
Any way to extract the names and numbers which would work consistently across all the items!
View 4 Replies
ADVERTISEMENT
Feb 27, 2007
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.
View 9 Replies
View Related
Jan 8, 2008
I have two worksheets: A and B.
Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).
Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.
Any particular Issue# field may have several issues in it, delimited by a comma.
Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)
This is a unique list of issues#'s.
All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.
A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list
There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)
I really hope that makes sense, but if not...
Here's the best example I could come up with: ....
View 9 Replies
View Related
Aug 8, 2006
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...
I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.
View 6 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Oct 23, 2009
a smart formula (no VBA) that will reverse the order of a list of names - parsing into separate columns.
The formula needs to be able to recognize/distinguish that if a comma(s) is present, then the names are listed as last, first middle. If there is a period this means the name has a middle initial or that a suffix is present. So,if the names are listed as last, first and middle then in the next columns will have in the proper order first name, middle name or initial (keep the period) and last name (loosing the comma of course).
If the original name order is in normal order - first middle and last then the formula should just pass through without any change. Would need to preserve generational titles such as Jr., Sr., III etc. Not sure where the best place for the generational titles should end up. Probably as part of the last name. See examples:
Col ACol BCol CCol D Col E
William J. ClintonWilliam J. Clinton
Churchill, Winston Leonard Spencer Winston Leonard Spencer Churhill
Bates, JohnJohn Bates
James Knoll PolkJames Knoll Polk
Taft, William H.William H. Taft
Carter, James, Earl, Jr.James Earl Carter Jr.
Rockefeller, John Davidson, IVJohn Davison Rockefeller IV
Gates, William, H., IIIWilliam H. Gates III
St. James, Michael, Ken Michael Ken St. James
CherCher
I tried using many variations of FIND, SEARCH, LEN and searching all the Excel boards to no avail.
View 9 Replies
View Related
Jun 23, 2014
So i have many different list of items that i have supplied, pretty much shopping lists. I am now trying to create an inventory of my warehouse and i would like to see what i should buy more of and less of. is there a certain table or template or something i can plug in all my lists and have excel break down all the data and tell me whats what on all my info. i have about 45 different lists and each list has an average of about 150 line items.
View 1 Replies
View Related
Feb 17, 2009
I am building an inventory application in 2007. I need to track the movements of the items so I was planning on a worksheet containing the item no, date, location.
Since an item will move around and thus have multiple records, how can I extract only the latest date record for each item and store them in another worksheet ?
So I would have 2 worksheets, a. transactions, and b. current inventory. Or am I trying to reinvent the wheel. I am sure there are a lot of inventory applications already written - how do I find one? So, 2 question!..
View 5 Replies
View Related
Jan 27, 2013
I am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
[Code] ........
View 2 Replies
View Related
Apr 24, 2009
I would appreciate some pointers on how to write a function(s) or macro to do the following in Office 2003:
- I get a weekly report (from Quickbooks) in Excel format which shows inventory usage.
Showing the relevant sections:
Col D Name
Col O Qty
Col Q PxQ
The report also throws out Total is Col D, but only for Col Q, not Col O (which is needed)
So, R5 thru 7 may show Apples
And R 11 shows Total Apples
Then R13-14 may show Oranges
And R15 shows Total Oranges.
The report has about 400 lines currently, and about 50 items......
View 8 Replies
View Related
Jun 3, 2014
I would like to parse the below :
Say this appears in A column , I want to parse all the characters appearing after the numbers (i.e., 8.625% /5.875%/6.125% ) in B column - Any excel formula ??
Alere Inc. 8.625% October 1, 2018
Delphi Corporation 5.875% May 15, 2019
Delphi Corporation 6.125% May 15, 2021
So B should column like below
8.625% October 1, 2018
5.875% May 15, 2019
6.125% May 15, 2021
View 3 Replies
View Related
Mar 18, 2013
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
View 7 Replies
View Related
Mar 17, 2014
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
View 2 Replies
View Related
May 1, 2013
I need some type of vba or macro that can extract text in between html tags from a list of URL's.
For example
I have a list of urls in column A
Columns B,C,D,E - are where different types of information from the website will go.
So B contains values of text between a <title> tag </title>
C will contain values of text between a <object> tag </object>
and so forth ....
View 6 Replies
View Related
May 24, 2014
Im trying to organize a tools inventory list. Its supposed to be sort of like an order sheet for each department. But what I want to do is when I select either CARPENTRY, or any of the other trades from a drop-down list, I'll be able to select from another drop-down list items associated with that trade in particular. So eventually I want the master data hidden, preferably in another sheet. I searched all of Google but it seems a bit complicated to get it done. I've attached the sheet so that you can see what I'm talking about. MOVE IN MASTER LIST.xlsx
View 8 Replies
View Related
Oct 21, 2009
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
View 9 Replies
View Related
Dec 26, 2013
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
View 3 Replies
View Related
Dec 12, 2013
I created a new tab at the end of the spreadsheet. It is called "Current Inventory". Each Month in the year has it's own tab as well. What I want to do is have what is currently in inventory show up on the "Current inventory" tab on Line D of that tab. I know what is currently in inventory on the "Ending Inventory" line of each monthly tab. So if we are in January then the "Current inventory" tab on Line D will read off of the "January Inventory" Tab of the "Ending Inventory" Line.
View 2 Replies
View Related
Mar 4, 2008
(Excel 2007)
What I'm trying to create is a way to populate a quick inventory list with my bar-code scanner.
I've already learned that in order to have excel move to the next row each time a 12-digit SKU number is scanned, I'm going to have to utilize a userform text box.
My problem:
I don't even know how to begin coding the text box to accept a 12digit number, enter it into the next available blank cell (I'd like it to populate vertically) and then ready itself to accept the next available 12digit number.
My desired end-result:
My scanner is attached to a lengthy USB-Extension, so I can roam my store and quickly scan the inventory on display. For that reason, running back to my laptop and striking "Enter" each time makes the whole solution pretty useless.
Once finished, I'm assuming I could use excels count feature to tell me the quantity of the items I have on hand.
View 9 Replies
View Related
Sep 10, 2012
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
View 8 Replies
View Related
Jul 31, 2014
I need to compare two lists of client names. One list has 59k names to be compared to second list of 1000 names.
The list of 59k are listed in column D and the other list is in Column E. I tried conditional formatting but unfortunately some of the names are slightly different I.e. fair point communications vs fair point communications inc. I was trying to put in v lookup with a trim function but it didn't seem to be working.
View 1 Replies
View Related
Jan 20, 2012
i am brand new to this forum, and not so fluent in excel for more complicated tasks.
i have been looking for a solution for this online, but seem to be running in circles, so thought i would post here.
I want to make an shipping list using a barcode scanner. i have a master list that contains all of the inventory numbers and the relevant item data in each row. i would like to be able to scan the barcode of the item and have excel automatically look up the row from the master list and put it into the shipping list.
View 5 Replies
View Related
Jan 13, 2014
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
Aug 18, 2009
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
View 11 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Aug 26, 2008
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
View 9 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related
Nov 18, 2012
I have a large dataset that I need to push into a dictionary, into that dictionary I also have to create other dictionaries and this can go on until I fill the entire dataset into the dictionary. The problem is that I do not know beforehand what the key names will be so I need to find a way to get the key names.
I have the following test code:
Code:
Dim player As New Dictionary
player.Add "val1", "hello123"
player.Add "val2", "piet123"
[Code]....
how to get reference the key in a nested dictionary not using the keyname?
View 2 Replies
View Related
Feb 24, 2014
I'm trying to sort out a list of names from a website that publishes names in the following format:
DOE John
VAN GOGH Vincent
DA VINCI Leonardo
NADAL PARERA Rafael
JIMENEZ RODRIGUEZ Miguel Angel
What I'd like to do is get the names in the following format
John Doe
Vincent Van Gogh
Leonardo Da Vinci
Rafael Nadal Parera
Miguel Angel Jimenez Rodriguez
Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.
I found the following function, here: [URL] ...
but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.
View 1 Replies
View Related
