Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to compare two differnet cell ranges to see if they are the sa
I have two data charts on the same worksheet with each chart containing 14
columns and 79 rows. Is there a way to compare the two cell by cell and then output a "same" or "different" if and cell in the a row is different between the two data charts? Does this have to be done cell by cell or can it be done by ranges? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to compare two differnet cell ranges to see if they are thesa
Hi
As I don't know your data layout I have created this example, which hopefully can help you. Sub CompareRng() Dim Test As Boolean Dim Same As String Dim Diff As String Test = True ' Both data sets start in row 1 ' Data set A start in column A ' Data set B start in column T For r = 1 To 79 For c = 1 To 14 If Cells(r, c).Value < Cells(r, c + 19).Value Then Test = False End If Next If Test = True Then If Same = "" Then Same = r Else Same = Same & ", " & r End If Else If Diff = "" Then Diff = r Else Diff = Diff & ", " & r End If End If Test = True Next msg = MsgBox("Rows " & Same & " are the same" & vbLf _ & vbLf & "Rows " & Diff & " are different", _ vbInformation, "Row check") End Sub Regards, Per On 16 Jan., 17:32, Tom wrote: I have two *data charts on the same worksheet *with each chart containing 14 columns and 79 rows. Is there a way to compare the two cell by cell and then output a "same" or "different" if and cell in the a row is different between the two data charts? Does this have to be done cell by cell or can it be done by ranges? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to compare two differnet cell ranges to see if they are the sa
I have done something like this in two different ways
1 - Create a third chart and use this formula =if(A1=K1,"Same","Different") Use the upper left cell of each chart in place of A1 and K1 You could also use Conditional Formatting on one or both chart for example in cell A1 use the Conditional Format Condition 1 Cell Value - is equal to - =K1 (pick some format like Green text or shading) Condition 2 Cell Value - is not equal to - =K1 (pick some format like Red text or shading) If you make sure that there are no absolute references ($) in the formula you can use the format painter to copy this to the entire table. "Tom" wrote: I have two data charts on the same worksheet with each chart containing 14 columns and 79 rows. Is there a way to compare the two cell by cell and then output a "same" or "different" if and cell in the a row is different between the two data charts? Does this have to be done cell by cell or can it be done by ranges? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to compare two differnet cell ranges to see if they are the sa
Tom,
1) Use a formula like =IF(A2=M2,"Same","Different") were A2 is the upper left cell of table 1, and M2 is the upper left cell of table two. Then copy that over 14 columns and down 79 rows. 2) Use conditional formatting. To highlight the cells that are different, select the first table, then use Format / Conditional Formatting... "Cell Value is" "Not equal to" and then select the upper left cell of the second table, and press F4 until the $s disappear, like =M2. Then set the fill to red or some other highlighting color. HTH, Bernie MS Excel MVP "Tom" wrote in message ... I have two data charts on the same worksheet with each chart containing 14 columns and 79 rows. Is there a way to compare the two cell by cell and then output a "same" or "different" if and cell in the a row is different between the two data charts? Does this have to be done cell by cell or can it be done by ranges? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to compare two differnet cell ranges to see if they are thesa
I know this has been answered, however, an alternate solution that
accounts for different workbooks, ranges, or relative positions is below. Another benefit is that it only takes one cell. Just enter as a normal function in any cell. I'd appreciate any comments as I've never attempted to solve this problem before. Public Function CompareTwoRanges(rOne As Range, rTwo As Range) As String Dim lRdiff As Long Dim lCdiff As Long Dim arOne As Variant Dim arTwo As Variant Dim rCell As Range If rOne.Row rTwo.Row Then lRdiff = rOne.Row - rTwo.Row Else lRdiff = rTwo.Row - rOne.Row End If If rOne.Column rTwo.Column Then lCdiff = rOne.Column - rTwo.Column Else lCdiff = rTwo.Column - rOne.Column End If CompareTwoRanges = "They match." For Each rCell In rOne If rCell.Value < rTwo.Parent.Cells(rCell.Row + lRdiff, rCell.Column + lCdiff).Value Then CompareTwoRanges = "Discrepancies Exist." Exit Function End If Next rCell End Function On Jan 16, 10:32*am, Tom wrote: I have two *data charts on the same worksheet *with each chart containing 14 columns and 79 rows. Is there a way to compare the two cell by cell and then output a "same" or "different" if and cell in the a row is different between the two data charts? Does this have to be done cell by cell or can it be done by ranges? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare two or more ranges | Excel Worksheet Functions | |||
Compare ranges | Excel Worksheet Functions | |||
Using IF to compare ranges | Excel Worksheet Functions | |||
Compare Ranges | Excel Programming | |||
Compare ranges | Excel Programming |