Multiple Lookup: List Of Part Numbers Based On The Product Part Code
Feb 12, 2009
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
View 3 Replies
ADVERTISEMENT
Mar 9, 2013
I have an excel file having part code,name,vendor and Qty ( Quantity ).
My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,
View 2 Replies
View Related
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
Feb 21, 2012
I have two lists.
The first:
P1116
MC1070
BD1068
I want to check if any of these values are contained in the text of each item in the second list, that looks like this:
64K4014-4" , 64K1030-4"
64P1116-3"
64P1187-4" , 64p1117-4"
64P1330-2"
64P1405-2"
64MS1007-0.75", 64MC1070-2"
64K1062-3/4"
64K1061-0.75"
64CW1062-2", 64BD1652-2"
64NG1007-1 1/2"
64K1008-2", 64BD1041-1", 64BD1068-2"
64HS1002-6"
64HS1002-6"
64CW1068-2"
I tried using the =search() function in an array formula, but I can only get it to work for a single value in the first list.
View 8 Replies
View Related
Feb 22, 2008
We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.
Current working formula:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))
What I'm trying to accomplish, but it not working is something like this:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))
View 9 Replies
View Related
Dec 27, 2012
I have a list of part numbers which repeat when there are multple prices they were sold at. For each unique part number I need one median price. The list if of about 500 parts but with the various different prices the file is 3700 rows. How can i do this quickly? For each unique part number I need one median price.
View 3 Replies
View Related
Jul 16, 2009
I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)
View 2 Replies
View Related
Sep 30, 2009
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
View 2 Replies
View Related
Nov 18, 2008
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).
=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))
View 3 Replies
View Related
Feb 12, 2014
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
View 5 Replies
View Related
Jun 21, 2006
I have a question regarding searching in cells for a value, and returning corresponding data. This is what my workbook looks like:
Sheet1, cell A1 contains value "D600"
Sheet1, cell A2 contains value "V-1234"
Sheet1, cell A3 contains value "DB23"
Sheet1, cell B1 empty
Sheet1, cell B2 empty
Sheet1, cell B3 empty
..........................
1. search each cell value form Sheet1 column A, in Sheet2 column B
2. when a match is found, return the corresponding value of column A from Sheet2....
View 2 Replies
View Related
Oct 12, 2007
I am not sure if a vlookup can do the following.
I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:
British Pound
Danish Krone
Japanese Yen
I have created another spreadsheet 2 columns that would have something like:
column A/columnB
British/Yes
Danish/No
Japananese/Yes
This 2nd spreadsheet can grow and have more lines.
In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.
View 4 Replies
View Related
Aug 4, 2007
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.
Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
View 6 Replies
View Related
Nov 28, 2013
If the admins or you would like to change or recommend a change, I am trying to figure out what the code would look like for cells B22:B28. I need it to count the number of occurrences of the part number in A4:A17 and then combine the applications from the corresponding cells in column F (F4:F170 into a single cell (B22:B28).
On sheet 2 I would manually copy/paste the list of part #s from sheet 1 and remove duplicates. The add the formula that I can't figure out into column 2.
View 6 Replies
View Related
Feb 14, 2014
I have a USB scanner connected to a PC. Data is entered into Excel when the part number barcode on a box is scanned. I have two worksheets that I will be scanning part numbers into, one is Group A and the other is Group B. On Group A's sheet there is a list of Group B part numbers that will be used for the lookup procedure when the barcode is scanned. The same is true for Group B's sheet. The groups should never be mixed, so if part number "B005" is scanned into Group A's sheet it should cause an error message to pop up. I've attached a workbook as an example. Is it possible to do this with VBA?
Barcode Scan Lookup.xlsx
View 3 Replies
View Related
Apr 6, 2007
I'm trying to create a lookup process for my address part of my invoice, I want it so when I enter the first address it automatically looks up and enters the rest of the information (Title, Name etc.) I've been trying to do this with vlookup and I can't seem to get it to work. I'm just getting #Ref! returned.
My address 'database' (list of addresses etc) are on a seperate sheet to the Invoice.
View 9 Replies
View Related
Feb 21, 2009
Assuming 1st row is a header row
Sheet1, Column A
1230000_XL07 - WB OPS
1230001_XL08 - WB OPS
1230002_XL09 - WB OPS
Sheet 2, Column A
1230000
How do I lookup 1230000 and return 1230000_XL -07 WB OPS in B2
View 2 Replies
View Related
Feb 12, 2010
I am trying to do a VLOOKUP on a worksheet with a list of our Part numbers. The Part numbers begin with zero and go into the alphabet with anywhere from 3 digits upto 18.
When I sort the sheet Excel sorts the numeric by the number of digits in the number AND totally ignores the first zero. I can not format as numbers since again Excel drops the first zero. Therefore, when I do the VLOOKUP it will not look through the entire numeric list for the higher digit numbers. Is there anyway to resolve my VLOOKUP issue with indexing or?
View 9 Replies
View Related
Oct 31, 2008
I am trying to find a way to eliminate endings off of part numbers.
Below are the list of endings that I need to be eliminated from an extremely long list of variant part numbert. This list below may need to have additional endings added, but nothing will be removed. Please see attachment for example of part numbers that need below endings eliminated. Thanks!
R
T
G4
E4
RG4
RE4
TG4
TE4
G6
E6
RG6
RE6
TG6
TE6
/2K5
/3K
/250
/500
View 14 Replies
View Related
Mar 16, 2009
I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result.
Here's how the Data looks like:
OrderPart numbers
123A
123B
123C
123D
234A
234B
234E
346A
346C
Answer for above would be 2 orders that have A and B part numbers on the same order.
View 12 Replies
View Related
Jun 17, 2009
Im using a formula to identify new part numbers. The formula is: =IF(ISNUMBER(MATCH(A217,Existing!A:A,0)),"","NEW"). However you can clearly see from the attached that if has flagged a duplicate part number as new. Why would it do that? Check out A1368 in existing and A217 in new.
View 5 Replies
View Related
Jun 19, 2013
I have a column of part numbers in the format 530020109 but I would like to change to 530 0201 09.
View 4 Replies
View Related
Nov 2, 2011
I'm trying to run a script from a CAD software which exports property values into an Excel spreadsheet. I need to then sort the spreadsheet by part numbers (which are located in column G) but my script is not working correctly. My data range is A1:G50.
Const xlCenter = -4108
Const xlAscending = 1
Const xlYes = 1
Const xlSortOnValues = 0
[Code] ............
View 1 Replies
View Related
Jan 7, 2007
I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08
when I want it to sort like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08
The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.
At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5
I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.
I tried creating a custom list, but there are just too many varieties to list them all.
View 8 Replies
View Related
Jun 16, 2009
I need to remove duplicate Part Numbers where other information in the cells will not match. In the following two examples, the only difference is that the COMP_ID: number is different, but for my purposes, the second example is a duplicate and needs to be removed. I have hundreds of rows of this type of information with various part numbers: ...
View 9 Replies
View Related
Sep 21, 2009
I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?
Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W
View 9 Replies
View Related
Aug 4, 2006
I understand that we can protect the VBA coding through check the "Lock project from viewing in the Tools>VBAProject Properties. However I'll still need to open some of the code for other users to customize the program while some of the important code are protected.
View 7 Replies
View Related
Oct 12, 2009
I have a list of part numbers and a new drawing number and old drawing number. I'm am trying to put this list on the second page of a workbook and write a code on thie first page that allows a person to enter the part number and the old and new drawing numbers will be displayed.
View 3 Replies
View Related
Mar 20, 2008
I have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices.
I have one part number in Cell say A1, Its price in Cell B1
i have another same part number in A2, its price in Cell B2
(The macro has grouped them together)
Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers)
This process is to be repeted in the entire worksheet.
Sub InsertRow_At_Change()
'part number
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 1).Value Cells(X - 1, 1).Value Then
If Cells(X, 1).Value "" Then
If Cells(X - 1, 1).Value "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Jan 19, 2010
I have a list of about 200 parts that shipped. However, each part shipped several times over the last year. I probably have 3,000 - 4,000 lines total.
What is need is each part once, with the last shipped date.
View 9 Replies
View Related
