Comparing Cells in Excel (not as easy as it sounds) 1

[GTstartup]

I hope you can help me with this one or at least point me in the right direction. I will try to explain it as simply as I can.

I have 2 columns of data that are similar but one has been revised. I want to compare the cells in the 2 columns and report in a 3rd column if the individual cells are the same or if they have been revised. I have tried the 'exact' function and this works well to find differences. However, if one column is different because a line was inserted, then it reports all cells after the inserted cell, not the same. Is there someway around this?

example of the problem with Exact Function
A B C
123 123 TRUE
456 456 TRUE
789 789 TRUE
ZZZ ABC FALSE
ABC DEF FALSE
DEF GHI FALSE
GHI JKL FALSE
.......................... etc

and what I want to happen

A B C
123 123 TRUE
456 456 TRUE
789 789 TRUE
ZZZ FALSE
ABC ABC TRUE
DEF DEF TRUE
GHI GHI TRUE

Or something along these lines

Thanks in advance

 

[IRstuff]

I don't understand. The function is working correctly. Why are you not inserting an entire row?

TTFN

 

[GTstartup]

Sorry, it's kinda hard to explain. The Exact function is working correctly as you state. But it's not doing what I need it to do. I need a function to compare columns and to denote those cells that are different. I need it to ignore the fact that some cells might appear to be different because a row was inserted. Inserting a row in one of the columns shifts the cells to be compared so that they are no longer in the same rows. See what happens in the example 1 above when zzz is inserted in column A. A is the same as B except that zzz is inserted in column A. The exact function then returns a FALSE after this inserted data

 

[jghrist]

Can you sort the columns before comparing them?

 

[GTstartup]

Columns cannot be sorted unless I can "unsort" them back into the original order after comparing.

 

[TomBarsh]

The problem relates to a feature of Excel: when you insert an individual cell the other cells in the same row or column are also moved (to right or down); Excel then updates cell references in any formulas that referred to the original (now moved) cells. However, sometimes this is not what the user wants (as in your case).

Using the data of your example, and assuming that cell A1 contains the first value in your data, "123", then the cell C3 could have the formula "=exact(A1, B1)" and evalutes to "True".

You can eyeball that cell B4 has contents "ABC" that match cell's A5, so it is natural to insert a cell at B4 and move the remaining cells in column C downward, thus making cells B5, B6, B7, etc, match with cells A5, A6, A7, etc.

However, as you insert the new cell at B4, Excel updates the formulas in cells in column C so that the "Exact" function refers to the same cells as before the insertion. ie: cell C3 has the typical formula "=Exact(A3, B3)" but now cell C4 has formula "=Exact(A3, B4)" because the old cell B3 has been moved down to position B4 and Excel assumes you want to continue referring to the "same" cell.

I have run into this countless times in my years of Excel'ing. Maybe there's a setting to work around this but I couldn't find one just now. I always just take the easy way out...simply copy down the original formula from somewhere in the same column (column C in your case) so that the formula with my desired cell references are applied.

 

[CajunCenturion]

If I understand, they you can maintain three row variables, one for each of the comparison columns, and one for the results column.

In an abbreviated fashion just to present the general idea:

Row1 = 1
Row2 = 1
ResRow = 1

While Looping
   If (Range("A" & Trim(Row1)) = Range("B" & Trim(Row2))) Then
      Range("C" & Trim(ResRow)) = True
      Row1 = Row1 + 1
      Row2 = Row2 + 1
      ResRow = ResRow + 1
   Else
      If (Range("A" & Trim(Row1)) = "") Then
         Row1 = Row1 + 1
      Else
         If (Range("B" & Trim(Row2)) = "") Then
            Row2 = Row2 + 1
         Else
            Range("C" & Trim(ResRow)) = False
         End If
      End If
      ResRow = IIF ((Row1 < Row2), Row1, Row2)
   End If
End Do

In any event, by maintaining three separate row counters, you can varying the rows independantly for comparisons and results writing.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[IRstuff]

My point was that if you insert an entire row, then the entries would still line up and give you the correct result from the test.

TTFN

 

[GTstartup]

I told you guys it wasn't as easy as it sounds! TomBarsh outlined the problem very well. As he says, I can "eyeball" when the data becomes the same again and manually adjust say, column A, to align once again the rows. In this case then the exact function is no longer referencing the new cells but the original cells before adjustment. Then I have to manually adjust the Exact function and copy and paste it back to Column C. Even using this crude method is a pain.

Cajun, thanks, but the number of rows varies. Will your code still work?

This may be one of those things, its just easier to do manually. I hope you can prove me wrong

 

[GTstartup]

IR,

Sorry I misunderstood your reply. The columms come from different spreadsheets, cut and pasted together to compare side by side. One column may have cells additional to the other or less than the other. In my example ZZZ is an additional entry in column A.

To use a real world example. Say you wrote some code and later it was modified. The modifed version has 5 extra lines in the middle. If you compare them in columns side by side the Exact function wil return TRUE until it meets the new data and then FALSE from this point on.

 

[CajunCenturion]

I think so, . I am not specifying the loop boundaries because I don't your application well enough to do so. However, it's likely that WorkSheet.UsedRange.Rows.Count may be used to determine the number of rows actually in use.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TomBarsh]

Try this VBA code: Select the cell that you want to move down, then run the code (put a button on your worksheet or create shortcut key strokes); the cell will be inserted and all cells in that column are moved down, then the formula is copied down to the bottom of the third column so that the cell references are correct.


Sub insertNewCell()
'insert a new cell at selected position;
'remaining cells are moved down
Selection.Insert Shift:=xlDown

'copy the old formula down the third column
'so that cell references are correct
Range(Selection.Offset(-2, 1), Selection.Offset(-1, 1).End(xlDown)).FillDown

End Sub

 

[EGT01]

You could try to use the Indirect function with an additional column that is used to define your row numbers. In this case, the formula in C1 would be
=EXACT(INDIRECT("A"&D1),INDIRECT("B"&D1))

You can then copy that formula to your other cells in column C.

Then if you insert a cell in row 4 of column B as in your example, the formula in cell C4 will not change and you maintain the row evaluation of A4 to B4.
[tt]
A B C D
123 123 TRUE 1
456 456 TRUE 2
789 789 TRUE 3
ZZZ ABC FALSE 4
ABC DEF FALSE 5
DEF GHI FALSE 6
GHI JKL FALSE 7
[/tt]

 

[GTstartup]

Thanks Guys,

Will try them all and report back in a couple of days.

 

[jghrist]

You can sort the column and get back to the original order by including a column of serial numbers in the sort. To get the original order back, sort on the serial number column.

 

[bltseattle]

I would do it sort of like EGT01 except instead of an extra column and INDIRECT I would use OFFSET. OFFSET is unaffected by changes in columns A & B, if setup like I show it below.

Leave ROW 1 with column headers and no data, then use this formula in cell C2 and fill down.

=EXACT(OFFSET($A$1,ROW(C1),0,1,1),OFFSET($B$1,ROW(C1),0,1,1))

This would still be corrupted by inserting/deleting cells from Column C, but not by changing the data in Cols A&B. You could probably work around this by using a variant of COUNTA instead of the ROW function as I've used.

 

[melone]

=IF(AND(ISERROR(MATCH(C4,$A$4:$A$700,0)),NOT(ISBLANK(C4))),"Diff",IF(C4="","","Same")) is a bit of code that I used before. Does this help?

 

[GTstartup]

Thanks everybody, the best suggestion for my particular application (and skill level) was Melone's. It compares not just the cells side by side but one cell against any in another range. In my case any added data would not show up at any other place in the range so it worked perfectly.

Thanks Melone!!