×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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



RE: Comparing Cells in Excel (not as easy as it sounds)

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

TTFN

RE: Comparing Cells in Excel (not as easy as it sounds)

(OP)
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

RE: Comparing Cells in Excel (not as easy as it sounds)

Can you sort the columns before comparing them?

RE: Comparing Cells in Excel (not as easy as it sounds)

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

RE: Comparing Cells in Excel (not as easy as it sounds)

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.

RE: Comparing Cells in Excel (not as easy as it sounds)

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:

CODE

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

RE: Comparing Cells in Excel (not as easy as it sounds)

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

RE: Comparing Cells in Excel (not as easy as it sounds)

(OP)
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

RE: Comparing Cells in Excel (not as easy as it sounds)

(OP)
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.

RE: Comparing Cells in Excel (not as easy as it sounds)

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

RE: Comparing Cells in Excel (not as easy as it sounds)

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

RE: Comparing Cells in Excel (not as easy as it sounds)

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.

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

RE: Comparing Cells in Excel (not as easy as it sounds)

(OP)
Thanks Guys,

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

RE: Comparing Cells in Excel (not as easy as it sounds)

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.

RE: Comparing Cells in Excel (not as easy as it sounds)

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.

RE: Comparing Cells in Excel (not as easy as it sounds)

=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?

RE: Comparing Cells in Excel (not as easy as it sounds)

(OP)
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!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources