Multiple Lookup: List Of Part Numbers Based On The Product Part Code
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Part Cell Lookup Across Multiple Worksheets
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 Replies!
View Related
Filter Sheet Based On Certain Digits In A Column Of Part Numbers
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 Replies!
View Related
Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)
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 Replies!
View Related
Lookup Part Text
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 Replies!
View Related
Lookup On Part Of Cell Value
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 Replies!
View Related
Lookup Process For My Address Part Of My Invoice
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 Replies!
View Related
Part Numbers With Zero As First Number
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 Replies!
View Related
Eliminate Endings From Part Numbers
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 Replies!
View Related
Count How Many Orders Have Part Numbers
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 Replies!
View Related
Sorting Alphanumeric Part Numbers
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 Replies!
View Related
Removing Prefix From Part Numbers
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 Replies!
View Related
Remove Duplicate Part Numbers
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 Replies!
View Related
Formula To Identify New Part Numbers
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 Replies!
View Related
Grouping Same Part Numbers And Adding Their Prices
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 Replies!
View Related
Run Part Of Macro, Pause, Run 2nd Part
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 Replies!
View Related
Protect Part Of The VBA Code
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 Replies!
View Related
Group Part Numbers, Blank Line Btwn Unlike
i have a spreadsheet with 21,000 part numbers. I am trying to group the like part numbers, then leave a space between the unlike part numbers. right now my spreadsheet has a space between each part number and i want to eliminate that. but also keeping the part's qty, date, etc. with it.
View Replies!
View Related
Enter Only Part Of A Zip Code And Have The Rest Auto Fill-in
i have an excel sheet where i enter company information including Zip code. the zip codes around here all start with 797 and end with numbers from 01 to 12 i am looking for a way to enter just the last 2 numbers and have it automatically insert the full zip example: line one i would enter 01 and 79701 would show up in the box. I know there are ways to do this, just can't remember its been so long since I've had to use excel.
View Replies!
View Related
Copy Files Based On Part Filename
I have code that takes full cell with filename, looking for it in certain directory and copying to another directory. Sheets("Unique").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select For Each rng In Selection If rng.Offset(0, 4).Value <> "" And InStr(1, rng.Offset(0, 4).Value, "http") = 0 Then FileCopy SourcePath & rng.Offset(0, 4).Value, ThisWorkbook.Path & "" & DestPath & "" & rng.Offset(0, 4).Value End If Next rng I want to change the code that it will copy based on number. Any suggestions? Example: I have range of filenames. 10989478.MEC0388A-L0_RoHS_globemotors_1.pdf 10989495.Keystone_M1.4cat. zip 10989559.MEC0388A-L0_RoHS_qualtek.pdf I want to copy files starting with 10989478, 10989495, 10989559. I mean ignore the part after "dot".
View Replies!
View Related
Highlight Part Of Cell Based On Input
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains Raiders @ Niners and another cell containing "home" or "away" indicating the winner. Based on the home/away cell, I want to either highlight Niners/Raiders.
View Replies!
View Related
Sum Based On Part Text Criteria
I tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.
View Replies!
View Related
Multiple Product Order Form With Price Lookup
On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is: 1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix. 2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet. 3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table. Currently this will be put into a macro and assigned to a button. The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it. I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.
View Replies!
View Related
Copy Data From One Part# On Sheet 2 To The Same Part# On Sheet 1
What I am trying to do is automatically move the date received and the total qty received from the Rcvg. Log sheet to the RFMs Release sheet. The problem is: 1) I don't know what I'm doing 2) On the Rcvg. Log several part numbers that are the same with different Qtys. 3) on the Rcvg. Log the are several part numbers that are the same using different programs. What I would like to do is combine all the same part numbers with the same program add the total Qty received and paste the date received and total Qty into the RFMs Release sheet in the proper part numbers row. Is this possible? Attached is a shortened copy of the spreadsheet normally there about 3000 entries.
View Replies!
View Related
Lookup Value From List Based On Multiple Conditions
I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.
View Replies!
View Related
Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View Replies!
View Related
Add Text Based On Part Of Another Cell Text
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?
View Replies!
View Related
Poker Part 1 Of 2
Like the one at the casino machines. A1 to A52 like this : A1 value = As (Ace spades) A2 value = Ah (Ace hearts) etc...etc.. Randomize like this: Sub Makerandomlist() Dim v As Variant With Worksheets("Sheet1") With .Range("A1:A" & (Range("A65536").End(xlUp).Row)) v = .Value .Offset(0, 1).Formula = "=Rand()" .Resize(, 2).Sort Key1:=.Offset(0, 1) .Offset(0, 1).Value = .Value .Value = v End With End With End Sub Transpose [A1:A5] in [C1:G1] In a hidden column 52 JPG's (all cards in a deck) using cases how can i do the equivalent of fifty two If's ? If [A1] = AhThen ActiveSheet.Shapes("Picture 3").Copy Range("C1").Select ActiveSheet.Paste End If
View Replies!
View Related
Enter System For The DF Part
In MS Query: Like '%ST%DF%' Works fine.and returns: DOCIDREVISION 31ST-9DF02007 40ST-9DF01006 73ST-9DF01016 74ST-9DF01011 74ST-9DF02004 I want to use the [Enter system] for the DF part. When I try to use the following in MS Query: Like '%ST' & [Enter Sys] & '%' the pop up window appears but after entering DF I receive an error: Missing Right parenthisis or illeagal variable?
View Replies!
View Related
Search Certain Part Of Cell
i have a array table all the rows of data get a unique file number , no duplicates. the file number format is 1.01, or 241.00, or 5132.03,etc. all numbers to the left of the period symbol are "file info" reference all numbers to the right of the period symbol are "bills" found inside the file #. there can be any amount of file #'s, always starting from 1 thru as many as there can be (apprx. thousands of files). and the bills can be from .00 thru .99 my search field ($r$9), need to search or find only the exact match to the left of the period. NEED to search for file 1, i need the array table to kick back only the file numbers 1.01 and 1.65, not 241.00 not 3.01. how can i search only the text before the period to the left and exactly what is entered into my search box?
View Replies!
View Related
Getting Part Of Name Of Cells In A Different Worksheet
Is there a way I can write a formula to get names from a different worksheet if I type part of that name in the active sheet. Say Sheet1 contains cells like 1026 Wild hard nuts fruits -Named Fgl1026 1028 Wild Soft nuts fruits -Named Fgl1028 In active sheet, when I type Fgl1026, I want the next cell to left of the activecell to show Wild hard nuts fruits. [img][/img]
View Replies!
View Related
Return Part Of A String
TX_jhfft_DATA.05-32_65 TGIF I need to take out the number in the middle, from the period to the space. The number varies in length so just using the mid function doesn't work. Does anyone know how to modify the mid function to return all chars between the period and the space or is there some other function that may work?
View Replies!
View Related
Copy Part Of Text
My file is included with alot of cell in the following format: aaaa. bbbbbbb (ccccccc) I want to copy the first part (aaaa) into column A and to copy the second part (bbbbbbb) in column B. Both part can vary in length: in this case their length were 4 and 7 but basically they can have any length. How can i do this. See also the attached file.
View Replies!
View Related
|
TRACKER
