Break Up Inventory Number Into Three Parts

Aug 16, 2007

I am having some difficulty with a macro. I have a column that contains a series of inventory numbers. I need to break it up into three parts and then have each part put into a new column. Here is an example of the data:

Column B = list of inv no.s such as ABC123.45 I need to divide it into three parts and put each part into a new column so a new column X would get the ABC part, a new column Y would get the 123 section, and a new column Z would get the 45 ** ( I dont need the decimal between 123 and 45) .

View 8 Replies


ADVERTISEMENT

Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet

Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View 3 Replies View Related

Inventory - Match Part Number To Multiple Vehicles Sharing That Same Number

Jun 3, 2013

I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.

Vehicle
Oil Filter #
Also used with
Dodge
51515
Honda
54565
Toyota
51515

I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.

View 4 Replies View Related

Separating The Parts Of A Mixed Number

Nov 4, 2007

Is it possible to separate the parts of a mixed number? I have a cell in which is generated a decimal number. I can convert this to a mixed number. What I need to do from there is extract the whole number and the numerator.

View 11 Replies View Related

Print Parts In Portrait Parts In Landscape

Dec 21, 2006

I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.

View 3 Replies View Related

Number Format For Inventory

Mar 6, 2007

I'm using excel as an inventory database but need to know how to change the number format according to what the user is keeping track of. For example drums, bags , cylinders ect...

How can i change the number format to display the unit of measurement added by user?

View 8 Replies View Related

How To Print Multiple Inventory Labels Based On Inventory Levels

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

Pulling Inventory Number From A Range

Jun 9, 2009

I have an inventory list (non-sequential) and I want to pull only the items with id codes that are with in 4 different ranges.

I would like to populate column A with the results, search paramiters are in Q3 and S3.

I can get it to work on a small scale but my inventory list is 10143 rows long.

View 3 Replies View Related

Number Of Days (Sales) Inventory

Jan 27, 2010

I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days.

I've seen a number of formulas around... and honestly am not sure I'm on the right track.

On the attached I have used the following:

(Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)

View 6 Replies View Related

Count The Number Of Times :: Regional Inventory

Mar 3, 2009

I'm trying to filter data into a cell that meets certain criteria...

I would like to count the number of times a sku is found in each region in each month... daily inventory counts are recorded.. the date is recorded as MM/DD/YYYY...

is sumproduct my solution? I'm getting errors, specifically #NAME?

=sumproduct((sheet1!L:L=SKU)*(sheet1!M:M=Region)*(sheet1!C:C>=1/1/2009)+(sheet1!C:C

View 9 Replies View Related

FIFO Inventory- COGS & Inventory Valuation

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

Eliminating Sold Inventory From The Inventory List

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

Code To Break A Number Down Into Groups

Jun 10, 2009

i need code that can look at cell d23 and take that number( say it is 13) and break it down into groups. that have 3, 4 or 5 per group. in this example 13 can be broken down into

View 3 Replies View Related

Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items

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

Break Links Of Number Of Workbooks To Master Workbook?

Aug 8, 2013

I have several hundred workbooks which are linked to a master workbook from where the workbooks draw their information. Now I would like to break all the links in all of the several hundred workbooks to this master workbook and retain the information. Is this possible without having to open each workbook individually and break the links via the 'edit links' option?

View 1 Replies View Related

Search Parts Of Columns Looking For Matches In Parts Of Other Columns In A Row

Mar 1, 2014

I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...

Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith

In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.

In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.

VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.

View 3 Replies View Related

Summing Variable Number Of Cells And Parts Of Cells

Mar 18, 2014

In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)

But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.

View 2 Replies View Related

Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time

Mar 3, 2009

I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!

View 4 Replies View Related

Page Break By Asking The Input File For Page Break To Be Done

Dec 9, 2008

The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.

For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".

I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :

Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))

View 12 Replies View Related

How To Move Parts Of A Row

May 30, 2014

I am trying to move some parts of a row (From G:AE) where text is found in G Column. It moves it back 1 column from G

[Code] .....

Attached File : ozeform.csv‎

View 4 Replies View Related

Cutting File Name In Parts

Mar 28, 2014

I have some files on disk where I need to copy a part of the file name to make a search in our database. The files are PDF. They can have a long name of short. The only thing that does not change is the last part.

The last part ends like this:
1234_vw golf_Es_1234567_20140321.pdf

I'm interested in the part in red to be taken out and put in a cell to the right of the file name.

I used the below code that I found googling. Now I just to cut the part out in red to be able use it in my search in a easier way.

[Code] .....

View 4 Replies View Related

Seperating Parts Of A Phrase Using Vba

Aug 3, 2009

I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"

I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State

Is there any way to do this within the code?

View 5 Replies View Related

Copy Only Used Parts Of A Range

Dec 19, 2009

I'm making a project where there's a list of skills and next to each skill is a cell where the user can put how many skill points are in that particular skill. Now, on a separate worksheet is the place where it shows the consolidation of what skills they know, and how many points are in each. My question is, how can I copy over these skills and how many ranks are in each, without copying over the skills in which they put no points.

View 11 Replies View Related

Combining Parts Of Two Cells

Jul 27, 2009

I have a list of dates in column A as follows:

11/1/2015
5/1/2012
8/8/2013
10/22/2015
4/1/2014
4/15/2014

In column B I have set of data as follows:
C 2010@101.0
C 2011
C 2011@101.0
C 2012@101.0
C 2013
C 2013
C 2014@101.0

I need to combine the day and month only in column A with the year (in positions 3-6) in column B

As an example I have "11/1/2015" in column A and "C 2010@101.0" in column B. The desired result in column C is "11/1/2010".

I have tried using the left function in column A with the mid function in column B but haven't had any luck so far.

View 2 Replies View Related

Cutting Columns In Two Parts

Nov 28, 2013

I have some values in column A which looks like those in table which is shown down bellow. I have somewhere in the middle of that column one empty cell. Of course i have empty cells after the last value. I want formulas or macro or anything which will cut that column in that empty cell and all cells bellow the empty one put in column B from A1, and all cells above the empty one put in column C from A1. I tried everything but i have problem because i dont know how to eliminate empty cells bellow the last cell with value.

ABC
117
228
339
44
55
7
8
9

View 3 Replies View Related

Create A Userform That Has Several Parts To It

Aug 15, 2007

I want to create a userform that has several parts to it.

1stly i want to copy information into the text boxes from a macro.

2ndly the number of text boxes could be infinate (in theory), i want 1 for each set of info

3rdly i want a little option tab box thingy for each box,

4thly the boxes and userform to adjust to fit the optimal size

View 9 Replies View Related

Protecting Parts Of A Sheet.

Dec 20, 2008

I am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.

View 9 Replies View Related

How To Select Parts Of Text

Mar 1, 2010

Does anyone know of a way to select fragments of the following text?

The string is "toby.cameron1".

I need to be able to select just the "toby" and "cameron" parts.
Also this string is variable in that it might be "james.brown" or similar without the number at the end.

I can't use a Left or Right statement because of the varying length of names and the uncertainty of having a number at the end.

Is there a way I can copy the start of the string until the period. And do the same from the end but ignore the number whether it's present or not.

From that, is there anyway to copy the numbers off the end of a string?

i.e. my string is ASY-623 or DRGN-12 or WDSR-7

The string will always be letters a dash and then numbers.

How do I copy only the numbers?

View 9 Replies View Related

Separate Decimal Parts

Mar 1, 2007

I have to make a report which calculates hours worked by employees over a weekend.The hours are stored in variable ,part of code is as follows:

For j = 4 To 32
startday = Weekday(thisDate, 1)
If startday = 1 Or startday = 7 Then
dummy = 0
tempnd = Cells(i, j)
weekndsum = weekndsum + tempnd Else
dummy1 = 1
tempdys = Cells(i, j)
weekdysum = weekdysum + tempdys End If
thisDate = thisDate + 1
Next j
Windows("Report.xls").Activate......................

View 2 Replies View Related

Save Parts Of Workbook

Mar 27, 2007

way to save just a work sheet insteed of the whole workbook.

it is because i have 9 sheets a main menu and 8 differant departments the idea of it is is for a stock take and i want to save them daily so i can goback over them at a later date.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved