Lookup Value Based On Specified Number Sequences?
May 8, 2014
I need to come up with a formula that will lookup a value in a table based upon a specified number sequence in another table and then add that value to another number/price that corresponds with that specified number sequence.
In the attached worksheet you will see that there are two groups of tables (A & B). I want to take the prices in group A in columns B:E and then add the values at the top of the page in columns G:J based upon specific numbers to the right of the decimal place in column A under "Rate".
So in the example at the bottom you will see that the first rate in column A is 5.125. Since the rate ends in .125 then it should be using the value of 2.57 from J2. So it should take the price in B27 (108.328) and add 2.57 to give you 110.898 in M27.
My problem is that the rate sequence wont always be the same and neither will the values in columns G:J. The only constants are that the rates will end in 1/8ths and the values will be based upon which 1/8ths they end in. For instance; In the example the rate range goes from 5.125 down to 3.75 but sometimes it may be 5.625 down to 4.25.
View 12 Replies
ADVERTISEMENT
May 6, 2014
The sheet I have attached has three tabs one is a master sheet another is the production input and the last is an engineering input section, I have coded the production input section to input into cells and email the engineers when they have a new job, the issue i have is with the engineering input.
The engineers can currently go into this sheet and input the job number onto the engineering input section and it will show them the current issue from the master sheet (I have already entered 1002 in it), what I want them to be able to do is input into the white sections below the description and when they click on update entry a macro takes the job number entered into B3 and saves the information onto the mastersheet in the correct column/row which corresponds to the job they are updating.
However the engineers may need to go into the same job several times and update different information therefore the fields need to not paste into the cells if the input sections are blank.
Masterfile.xlsm
View 1 Replies
View Related
Oct 18, 2013
I have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
View 6 Replies
View Related
Jan 12, 2009
I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.
View 8 Replies
View Related
Sep 7, 2009
I am doing research and I have a list of over 100 small dna sequences that I would like to see if there are any similarities between them. I have put them in individual cells but I dont know how or what function to use.
View 11 Replies
View Related
Jul 18, 2006
I need to create a warning when specific values sequences are input from a
drop down menu. My example:
I have 31 columns (days in month) and have 25 rows (peoples names) from the
drop down box I have the following values-These are shift designations
(V1,V2,V3,V4, SV1,SV2,SV3,SV4, A1,A2,A3,A4,N,R1,R2,R3,DB,C).
If the selections in cell between B1:AF25 are either SV 1-4 or A 1-4 I want
a warning to appear IF a value of V 1-4, R 1-3, DB or C are attempted to be
put in the immediately following cell.
Example: Cell B3 has A-4 and in cel C3 they attempt to put N, then it
should give a warning "This is not a recommended shift sequence".
View 11 Replies
View Related
Nov 18, 2008
I have 70 sequences of binary coded variables each, which I would like to compare in terms of overlaps for the number "1", e.g.,
a1 1 0 0 0 0 1
a2 0 1 1 1 0 0
a3 0 1 1 0 1 0
.
.
.
a70 0 1 0 0 0 0
How can I do a pairwise comparison in Excel for the number "1" (ie how often does the number "1" occur at the same place for two sequences?).
I thought about a correlation matrix (that compares sequences a1-> a2, a1-> a3, a1->a4, ...., a1-> a70; a2->a3, a2-> a4, ...., a2-> a70; .....; a69-> a70), but am looking for an easy way to compute this in Excel.
View 6 Replies
View Related
Apr 1, 2007
I want to write a formula that counts sequences.
Details of Data:
Imagine you roll a 4-sided dice! The four sides of the dice are 1,2,3 and 0.
Sequence needs to be 5 in a row of either 1 and 2, 1 and 3 or 2 and 3. The 0 is to be recorded, but 'skipped over' in the sequence.
When a sequence of 5 occurs, there needs to be a count to record how many more times the missing number takes until it arrives. For example, if the sequence of 5 is 1 and 2, and the count will be to see how long it takes the number 3 to arrive.
View 9 Replies
View Related
Mar 18, 2009
how to go about finding sequences and highlighting them.
here's an example list:
7600
7601
7602
7603
7604
7608
7609
7610
7611
now i need to find 3 things in this list.
1) how many 'batches' of sequences there are in this list. The numbers 7600 to 7604 are a consecutive sequence of five numbers followed by another sequence from 7608 to 7611. that makes two sequences. i need a formula for one cell that would specify how many sequences there are in a given list (in this case they're two sequences in this list).
2) is there any formula that can allow me to see each sequence highlighted in a different color (or any way to differentiate the sequences so i can copy paste them easily?). Pretty sure i'd have to use conditional formating on the entire list to do so but am clueless as to the implementation.
3) a formula for a single cell that can state the largest sequence in the list. in the list above the largest seuqnce is 5 numbers so that would be the value i'm looking for.
View 9 Replies
View Related
Oct 22, 2013
Worksheet cells A1 – A25 have sequential numbers 1 – 24
the following elegant code generates every permutation of six numbers from the list into Cols C – H
VB:
Option Explicit
Sub Combinations()
Dim rRng As Range, p
[Code].....
View 7 Replies
View Related
Nov 5, 2008
I have a spreadsheet tool that I use to calculate values on a set of variables, based on values on an initial set of other variables, for a large number of cases.
The calculations require a number of sequential steps to be repeated to get the calculated values for each case, and I am hoping there is a straightforward way to use a VB macro to automate this process.
Essentially, these steps are required to calculate values for a single case:
1. A data input sheet holds the values on the initial/input variables for each case. The first step involves copying the string of values for the case on the input variables into a sheet where the calculations are done.
2. As soon as the input string of values are pasted into the calculation sheet, the calculation formulas generate values for the case on the new, output variables.
3. The output variable string is then copied from the calculation sheet into a “results output” sheet. This copy/paste procedure needs to offset from the first row on the output sheet, so that the row number where the values on the output variables are stored correspond to the case ID. For example, case ID 10 is stored 10 rows down from the first row (i.e., on row 11). Case ID 100 is stored 100 rows down from the first row (i.e., on row 101).
As there are a very large number of cases, I need to have a “control panel” in place, where I can indicate the range of case IDs that will be processed. This takes the form of two cells – one indicates “From ID” and the other “To ID” (for example, “From ID” 1 “To ID” 100). The macro then uses this information to loop through the calculation steps outlined above for these case IDs.
View 4 Replies
View Related
Sep 28, 2006
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
View 4 Replies
View Related
May 28, 2009
I have the following two formula's:
=IFERROR(IF(X12="",X13&" "&MATCH(FALSE,INDEX(X13=X13:X1000,0),0)-1,X12&" "&MATCH(FALSE,INDEX(X12=X12:X1000,0),0)-1),"")
=MAX(FREQUENCY(IF(INDEX(X:X,12):INDEX(X:X,1000)="WON",ROW(INDEX(X:X,12):INDEX(X:X,1000))),IF(INDEX(X:X,12):INDEX(X:X,1000)="LOST",ROW(INDEX(X:X,12):INDEX(X:X,100)))))
The first shows the current sequence of events (WON or LOST) and the second the best sequence achieved.
They work fine but I would like to modify them to only use the data, if "P" is in column D of that row.
View 9 Replies
View Related
Sep 30, 2008
I have two sheets in Excel as below format. I would like to lookup (fill) “issue to” column of sheet2 in respect to sheet1. Please is there any funcations?
Sheet1
chqno Fr chqno toIssue to
1000110050Branch-1
1005110100Branch-2
1010110150Branch-3
1015110200Branch-4
Sheet2
Chq noIssue to
10002
10006
10155
10120
10008
10035
10151
View 14 Replies
View Related
Oct 26, 2006
I have a table of information which is sorted in ascending order. I have created a vlookup to find the highest value as close to 1 as possible.
Say it returns the value 0.95.
i now have another cell which i need to find the second highest value from the column of data.
however i dont know how to do it. because the second vlookup function just returns the 0.95 all the time and not the second highest value..
E.G.
Column A
0.70
0.75
0.81
0.84
0.87
0.90
0.95
So in the cell with the first Vlookup i get the value 0.95 returned.
I need then for the second cell to return the next highest value in this case 0.90.
Then third cell third highest
fourth cell fourth highest value etc.
View 9 Replies
View Related
Sep 7, 2007
i already have a random generator and when i have the result number i want to search the number from other worksheet.
View 5 Replies
View Related
Sep 8, 2007
i have this file that has about 12000 rows of numbers (a individual number can appear more than one). i'm trying to use a lookup to find if that number appears and if it does then bring back the amount next to the number.. however because the vlookup sees that the number more than one in the list it will bring back #N/A every time. Is there a way to get around this??
View 4 Replies
View Related
Nov 13, 2013
How to get the card# from staff ID as lookup value? ask.PNG excel question.xlsx
View 6 Replies
View Related
Apr 3, 2008
Due to the company that i work for doing alot of manuafacturing each and every day, i need some sort of look up.
For example, the currently excel sheet i use.
[IMG][/IMG]
As you can see i have many different Job Numbers that i use.... BUT
The problem that i am facing is, that these jobs sometimes are carried over to more than one day, [sheet bottom of screenshot] but also we might make the job one day and and then not again untill next week using the same Job Number.
What I Need
I need a way of searching for a job number across all 31 days and for it to total up the "Quantity (Qty)" of them all that we made upon that Job Number.
So lets say the Job Number 91294 was run on day 1 (shown in screenshot).
And it made 25.
But it then ran again 3 days later on the same Job Number 91294.
And it made 20.
I need it to be able to work that out the total for me across them days being 45 upon the Job Number 91294.
Things to Consider
The layout will NOT change, no extra rows or coloums will be added.
View 9 Replies
View Related
Oct 31, 2008
Im trying to lookup the first unique number in a column in a separate workbook and then return the value then the cell below would lookup the next unique number and return the value
91010111112121313700
700
700
7009929921442144218341834185018501892189219181918333433343400
3400
3400
3400347634763534
View 9 Replies
View Related
Aug 15, 2008
I've found Nth_Occurrence and it's frankly brilliant but I need to be able to return the occurrence number of a value in a range and it doesn't quite do what I need.
Using = COUNTIF(A$1:A1,A1) gives me exactly the answer I need but as I'm working with big ranges it's taking a lot of calculation time.
View 9 Replies
View Related
Aug 18, 2008
I am having trouble using a cell reference with a VLOOKUP formula. i have 2 worksheets each containing an invoice number column. the first sheet uses the VLOOKUP to find a shipping code from a range of values in the 2nd sheet (the range includes the invoice number, shipping code and 2 other columns). i can get the formula to work by simply typing the lookup_value into the formula like so: =VLOOKUP(7128,'Detailed List'!$B$7:$Q$1500,5,TRUE)
this gives me the correct result but what i need to happen is for the formula to take the lookup_value from the invoice number cell reference in the first sheet for that record and match it to the same value in the 2nd sheet. so instead of the code above it would read: =VLOOKUP('F10','Detailed List'!$B$7:$Q$1500,5,TRUE). the value of cell F10 is '7128'. however, when i try this i get a return of #N/A and no matter what variation i try it still produces the same result.
View 5 Replies
View Related
Aug 25, 2006
I have two columns of numbers (I'm going to call them Column A & Column B). There are 23 rows with these numbers. There is another column (Column C) which is also 23 rows of numbers. I am trying to find a way for a designated cell that falls between the two numbers in Column A and Column B to display the number in Column C. The numbers would work their way across the row. For example, if cell A1 had a value of 1 and B2 had a value of 10 and cell c3 had a value of .034, if the designated cell has a value of 7, I would want the cell below the designated cell to show the value of .034. I can't use the if/then function because of the 7 limit. I also can't use the vlookup because there is a range of numbers instead of an equal to.
View 4 Replies
View Related
May 18, 2007
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
View 2 Replies
View Related
May 5, 2009
I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View 2 Replies
View Related
Aug 11, 2009
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
View 5 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
Jul 28, 2012
I need to lookup the last date coupled to a serial number but with vlookup it takes the first serial number from the top of the database. Is this at all possible?
View 3 Replies
View Related
Nov 27, 2008
I have a spreadsheet on a weekly data with diferent reference names
e.g.
101
101
101
105
105
101
101
109
109
114
114
101
101
what I want is a formula to lookup the numbers from above and only extract a number once
e.g
101
105
109
114
View 9 Replies
View Related
Oct 16, 2006
I have a sheet that uses all vlookup formulas to populate the cells, and then some basic math calculation are done in seperate columns with the results. The problem is, even though the rest of sheet works fine one column is being read as text. I've tried many things to get Excel to read the column as numbers but nothing is working. So far I've tried everything I've found in Ozgrid (Paste special using add and multiply, using a working column)and the simple formatting things built into Excel, but nothing is working. All I need is for the column to be read as a number.
View 9 Replies
View Related
