Line Up Data With Tolerance
Jul 17, 2007
I have a large amount of data pertaining to many different runs in which I am given the mass to charge ratio of a compound. My goal is to identify these compounds, and to ease this process I am trying to find a formula that would “line up” near identical masses while providing a blank if no mass was found in that run. For example, if I have:
Run1
185.041
196.073
200.454
667.175
Run2
185.040
196.074
398.452
667.175
Run 3…etc
I would like to have it arranged:
Run1Run2
185.41185.040
196.73169.074
200.4540.00000
0.00000398.452
667.175667.175
It would be important to have some sort of tolerance setting, as the readings are not always exactly the same. Also, I’d normally have about six runs of data to line up.
View 8 Replies
ADVERTISEMENT
Feb 23, 2013
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Aug 20, 2014
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
View 1 Replies
View Related
Aug 8, 2009
I use this formula to calculate if a move is out of tolerance =IF(ABS(B1)>=1,"Y","N") This will work on most of the stuff we load. I would like to modify it to if b1 = "Crude" then use =IF(ABS(B1)>=.5,"Y","N"), or if B1 = "Resid" then use IF(ABS(B1)>=2.0,"Y","N"). Is it possible to include the 3 tolerance bands in one formula?
View 9 Replies
View Related
Mar 3, 2007
I am successfully using VBA to check a cell on one sheet against a cell on another sheet.
How easy would it be to check if this is the same OR less than 2 or more than 3? I have done part of this using nested formulas but cant find anything to help me with the syntax in VBA
The code I am using is
If W1.Cells(a, 3).Value = W2.Cells(b, 3).Value Then
View 4 Replies
View Related
Jul 6, 2007
I have two numerical columns of different length. One presents the signal and another one the background. Most of the background numbers are found in the signal column but they are not exactly the same and not located in the same rows. The numerical values are the same within some tolerance that I can specify. What I am trying to do it to remove all the background values from the signal column.
View 8 Replies
View Related
Jul 25, 2007
I am trying to replicate a method , it reads: 'any data point that changes by more than or less than 10% from the previous value, and does not remain at the new level for more than 6 days, should be removed.'
Thats a bit vague. The process is going to involve if statements, etc. I've attached an excel spreadsheet with some solutions I've tried. Neither of which work. The aim is to get the same answers as the highlighted column. Columns B and C replicate the method answer, but do not state the process. The letters L represent values less than 25. Its the letters D I'm trying to eradicate.
View 2 Replies
View Related
Oct 26, 2008
Below is a short segment of my excel spreadsheet:
A B
1020.00242-89.84
1020 -88.11
1021 -85.3
1021.494-80.41
1021.49 -86.98
1030.04 -89.4
1030.042 -88.26
1030.94 -79.98
1030.933-81.5
1030.961-85.87
1040.0418-88.77
1040.391-87.3
1040.291-82.94
1040.016-84.12
1049.82 -84.7
What I need to do is write a macro that will find duplicates in Column A, within a changeable tolerance, say 0.1 (10%).
After finding all duplicates within a tolerance in A, I need to make another "Master" worksheet with the Duplicates from A, and their counterpart in B. So if A1 and A4 where within 10% of each other, the "Master" worksheet would contain:
A1 B1
A4 B4
using the values, giving:
1020.00242-89.84
1021.494-80.41
I tried using SUMPRODUCT and some other functions but just can't seem to put my finger on this one. I'm sure it's not hard and am overlooking something.
View 9 Replies
View Related
Sep 13, 2007
I am trying to apply conditional formatting to multiple columns based on a percentage varience of the value in the first cell of each column. I'm sure this is not a new concept however I have been unable to find any advice when searching. I have attached an example of my problem as a picture speaks a thousand lines of code.
View 8 Replies
View Related
Apr 22, 2014
I'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
View 2 Replies
View Related
Aug 7, 2009
I need to be able to get a true/false from a tolerance percent.
Here is an example of what I am trying to do
cell a2 is Nitrogen
cell b2 is (Known gas%) 2.4800%
cell c2 is (unknown gas%) 2.4963%
cell d2 is =b2-c2 and I get the answer no trouble there. what I need is to take the answer in cell d2 and set a plus/minus 2% tolerance in cell f2 and get a true/false comparison.
View 4 Replies
View Related
Sep 4, 2008
a lookup and match of two values.
For example, in Sheet1 I have a unique transaction reference number and a £ value.
eg. 60231568 £38.05
In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...
60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q
The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.
View 9 Replies
View Related
Apr 8, 2008
I have an unknown number of rows that represent parts and 6 columns that represent part dimensions. I need to find and output matching pairs of parts that are within certain dimensional tolerance ranges.
For example:
Dimension 1 Dimension 2 to Dimension 6
Part 1
Part 2
to Part X
-I would need the code to check if Dimension 1 of Parts 1 & 2 were within a certain range and if Dimension 2 of Parts 1 & 2 were within a range etc. then (if all of them were true) output them as a pair. Otherwise, it would continue to search for paired parts. Once a pair is found, those parts can't be used again. How can I do this?
View 6 Replies
View Related
Aug 6, 2008
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
View 6 Replies
View Related
Aug 20, 2012
It's a worksheet for testing milk tanks. The issue is as follows:
Column F = Chart Error/This can be either a positive or negative whole number
Column G = Tolerance/This is always a whole positive number
Problem is I want Column H = Status to say "Pass" or "Fail" based on meeting the Column G number. But it's only the number itself (not whether it's Positive or Negative) that matters in determining the P/F status.
Examples:
Column F= -20
Column G= 15
Column H= Fails
or
F= 20
G= 15
H= Fails
The formulas I've been coming up with will show one or the other as Failing but not both + or - numbers if they fail to meet the G Column tolerance number.
View 6 Replies
View Related
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Feb 23, 2010
I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:
Col. ACol. BCol. C
Smith101
Jones512
Green65
Black1214
Smith 3612
Jones1512
Dual25
Green1225
I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
View 4 Replies
View Related
Nov 17, 2012
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
View 5 Replies
View Related
Aug 5, 2014
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
View 7 Replies
View Related
May 19, 2014
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
View 9 Replies
View Related
Feb 6, 2013
I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.
=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),
When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.
B C D
Atex ID#(unique id) Campaign name Site
Row 7 1st occurrence of IO# 777
Row 8 2nd occurrence of IO# 777
Row 9 3rd occurrence of IO# 777
View 2 Replies
View Related
Apr 30, 2013
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
View 2 Replies
View Related
Jun 21, 2008
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
View 9 Replies
View Related
Dec 8, 2007
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
View 9 Replies
View Related
Apr 10, 2014
I have a sheet called "Data", which has a list of individuals, and a sheet called "Timetable".
Using input boxes, I get from the user: number of sessions, frequency, session name and then I can get the following sessions to populate:
Date PatientSession Name
09/04/14 xxxx
10/04/14 xxxx
What I am trying to do is populate the "patient" column with the name from the "data" sheet, and then loop for all names, ie, ending up with
Date PatientSession Name
09/04/14 AA xxxx
10/04/14 AA xxxx
09/04/14 AB xxxx
10/04/14 AB xxxx
I have attached a sample of the sheet
View 5 Replies
View Related
Nov 29, 2013
I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code
View 2 Replies
View Related
Nov 6, 2009
With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.
for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.
View 4 Replies
View Related
Mar 30, 2014
I have a multiple line texts I want to convert it in single line like for Example:
The following contents is to be converted in a single line
contents:
[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on
Result:
[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on
View 4 Replies
View Related
Mar 14, 2014
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
View 5 Replies
View Related
