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 RepliesI 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.
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].....
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 RelatedI 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".
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.
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.
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.
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.
I have two sets of data to compare. One is real world data, the other is forecasted data. I have real daily data for every day over about 3 months. The predictions though, there may be 4-6 predictions for ever one real daily value.
What I'd like to do is have a function that looks at Column A's date (real daily value) and Column C's date (predicted data) and if Column C equals Column A, then divide corresponding data in Column B by value in Column D.
In the attached excel file I have two macros that create hyperlinks:
1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).
2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.
In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.
In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?
If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro.
Shape color changes as the hyperlinks are selected from sheet1.....
So I have a sheet with 600 or so rows of data on a sheet called "Agent_Summary". On another sheet in the same workbook I have a list 20 peoples names. I am trying to find a way to look at the 600 or so lines on the Agent_Summary in column A and see if the name in that cell matches any of the 20 names from the "Team" Sheet. If the name on Agent_Summary is not found on the "Team" sheet then it clears the contents of that cell and moves on. Then it comes back and Deletes the blank rows. I can make it look for any 1 of the 20 names and the code works fine. How do I turn it into something that will look for any of the 20 names?
Here is my code to look for 1 of the 20 names that works great.
Code:
Sub Clean_up_Agent_Summary()
Dim Cell As Range
Dim R As Long
Dim RE As Object
Dim Rng As Range
Set Rng = Worksheets("Agent_Summary").Range("A5:A600")
Set RE = CreateObject("VBScript.RegExp")
[Code]...
"Worksheets("Team").Range("A5")" is where my 1 name is and the rest are just below that. I am thinking some kind of an Array but I am stuck on getting that set up and working right.
i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.
View 3 Replies View RelatedI 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.
I have two cells which contain text, and I'd like to use IF function to compare between them. Something like: =(IF(A1=B1),"TRUE",FALSE"). But it seems like the function cannot do it with text..
View 3 Replies View RelatedI would like to compare 2 cells, Sheet1!B2 and Sheet1!B3
(
If sheet1!b2 is between the values of "D101" AND "D105", OR EQUAL TO "D201", OR EQUAL TO "D203", OR EQUAL TO "E110", OR EQUAL TO "E111", OR between the values of "H107" AND "H110" OR between the values of "H107" AND "H110" OR between the values of "H203" AND H205
)
AND
(
if sheet!b3 is EQUAL TO "E" )
then output a 1, if not, output a zero
The formula, which I deleted, excel reports back as being too long
I want to compare the value of D8 to the value of the last cell in Column D with data. I have the following code but am stuck! (This is code that I was writing from scratch and may not actually work)
Code:
Dim myRng As Range
Dim C As Range
Dim lr As Long
On Error Resume Next
[Code] .........
I am stuck on the "Else" statement...If they don't match then change the color of cell D8 if they do match I want to do nothing.
I want to do is create a function that will allow me to compare various cells. For instance, we have people scoring applications and I want to see if raters' ratings are within 10 points of each other. If raters differ by 10 points I want to print conf call on my spreadsheet (so I can schedule a conference call for raters to discuss the 10 point difference). If raters are within 10 points then I want to print "compute mean" (so I will generate a mean for each applicant).
I can get Excell to compare one rater against another (=IF(C3-C4>10,"conf call","calculate mean"), but I do not know how to make it compare all raters. For example, I have four raters and I want to be able to compare rater 1 to rater 2 and rater 1 to rater 3 and rater 1 to rater 4 and then rater 2 to rater 3 and rater 2 to rater 4; and lastly rater 3 to rater 4. Then if any rater dyad is different by 10 points I want to print our "conf call", but if there are no differences (by 10 points) then I want to print our "compute mean".
I converted a very large PDF to Excel. I want to run a logical formula that will compare a mailing address and property address. My formula goes like this: =if(a1=b1,"yes","no"). However, when I run my formula I am getting False for every line even though I can plainly see the answer should be yes. My guess is that it has something to do with the formatting of the text inside the cells that I'm comparing but they seem to be formatted the same. The strange thing is that if I manually type the information and run the formula, it comes out fine. I have more than 37,000 lines of text so I cannot manually retype the whole thing.
View 4 Replies View RelatedI am trying to code for the following conditions and will like to know how I can embed these conditions into one statement.
IF(AND(X>365, A>B), C*D
IF(AND(X>365, A<B), E*F
If neither of the conditions apply, then 0.
I'm trying to match and make judgement for my excel file and return as "OK" or "NG"
Eg.: New Picture.png
I've got a very big spreadsheet with over 40,000 lines.
The text is sorted alphabetically, and on its side I'd like to have a column displaying how different it is from the cell above.
Example:
A1 - Mediterranean Sea. -----
A2 - Mediterranean Ses. ----- 5.55%
A3 - Atlantic Ocean. ----- 100%
Basically, 'Atlantic Ocean' is 100% different from the cell above, but A2 has only one letter different (out of 18, including the space and the dot) compared with A1, so it's only 5.55% different.
This would help me find which cells that are supposed to have the same content, have errors and thus are slightly different.
I have multiple colums that I want to compare (A=F), (B=G)........
I want to see the difference of the two as shown in Blue column (example file is attached.)
Skærmbillede 2012-12-13 kl. 18.37.15.jpg
I am trying to write some code that will compare three cells on a row and if they match then it will delete the row.
Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)
I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
I need to create a micro that can compare the value of the cells and if it is the same, it will merge. currently my code is ......
View 11 Replies View RelatedIs there a way to compare two cells to see how many
numbers are same?
For example, A2 contains 16, 17, 19; B2 contains 16, 17; and I want to put
in C2 the result 2.
I'd like to compare two cells, and if the first cell is either >150, <161, OR =165 AND the second cell is ="E" to output a 1, and if false, output a zero.
I have =IF(AND(AND(Sheet1!B3>150, Sheet1!B3<161, Sheet1!B3=165 ),Sheet1!E2="P"),"1","0")
But i know that is incorrect because Sheet1!B3 can not be greater than 150 AND less than 161 AND = 165. I can't figure out how to make it >150 AND < 161 OR = 161.
I will like a macro to compare row one to row one on sheet 2 column A. If the number is the same then it will compare from the same row column K from sheet 1 and column m from sheet2. If it is less then copy to sheet4 and highlight in red, if more than highlight in blue and if equal green.
View 6 Replies View RelatedSome data
In Cell A1 This is a bag
In Cell B1 This is a bag with books
now in Cell C1 i need the mismatch words With books
C1 should show with books
I am now manually finding the words of cell A1 in Cell B1 and then writing the mismatched words C1 .
I have an excel spreadsheet with multiple columns of options.
In column A, I select either "yes" or "no" to activate other columns.
In column B, I have, let's say, lease terms; 12, 24 & 36.
I'm trying to find a formula that I can enter that will take all the columns I select as yes, and tell me if the lease terms match up.
So, if I select yes in cells A4 & A13, are the lease terms in B4 & B13 the same...do they match?