i wonder if there is a way to compare the formulas in two cells and not their result. I use a worksheet as a model and i want to track unwanted changes in the formulas between any new worksheets created and my original worksheet.
View 9 Replies
I want to design a macro that will compare the student's completed worksheet to the model answer worksheet. This will allow the class to work at different paces depending on ability.
For each exercise, the two sheets should look identical; and so, I would like the macro to highlight any different cells in the student sheet in a colour. I would also like cells with identical values, but different formulas to be highlighted - this is why I could not use the solution posted in : "Compare two worksheets and highlight the one sheet's difference from the other one".
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?
For Each Row In Range("Dev_Found") 'Loop through each row in Column C
If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then
blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error")
Exit For
End If
Next Row
I have below code which pin out numbers from IBAN in Column P. Its formulas run by macro. It works fine. But what i really need is that instead of putting the formula in the cell, is that it compare the value from the formula, with the excisting value , in each cell . And it mark the cells where there are a difference with yellow colour I have been thinking how to do this but cant really work it out. If some have a better solution its ok but i need a macro for it.
have a look at below code, which change the cell and correct the error, but which i want just to compare the values.
I have attached a sheet to test with.
[Code] .....
Attached File : Testforcompare.xls‎
I would like to rotate a Line Chart 90 degrees. I tried using an XY scatter chart but my Y-axis would be time values which are somewhat random but increasing. I would like to keep the spacing between plot points consistent (Y-axis spacing). I could simply use a number list for my Y-values to get consistent spacing but then I lose the time information. I don’t necessarily need the time value on the Y-axis if I could get the information to display when mousing over the plot point. Any ideas on how I could accomplish this?
View 6 Replies View RelatedI am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI 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.
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
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 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 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 RelatedI'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?
I have a question on the vba syntax. I have a column of data whose values decrease by 1 or so as I move down the cells. My data look like:
A B C D
999.1 111
1000 110
1001 109
And here is a code that I have written:
Code:
Sub ppm_sort()
Dim strrow, endrow, i, j As Integer
strrow = 1
endrow = 5000
For i = strrow To endrow
[Code]...
What this code does it, it first looks at Column B and pulls the values from Column A to C until the value in B hits 1. Then it calculates the average of the pulled values, after which it deletes the values in column A to C. If values in column B are greater less than 100, it does not calculate the average and just deletes the columns, moving the cells up. However, I ran into this problem:
A B C D
999.1 80
1000 110
1001 109
I still need to pull the values and calculate the average but since the value in B1 is less than 100, the code does not do so. So I tried to change the code colored red above to:
Code:
If (Range("B1:" & "B" & i).Value < 100) Then but excel gave me an error of 13 run time error. Basically, I need to figure out how to compare the values in a range to a number and combine if statement.
comparing cell data 1 from data 2 but what I need to extract are only the numbers and using a vlookup.
Data from 1 Data from 2 DN000254162 GSODN000254162 IN0513231 GSOIN0513231 CI/0003216535/BAH CI/3216535/BAH
