compare cells

Microsoft Office excel 2007 home & stude...
August 2, 2010 at 10:34:26
Specs: Windows 7
Here's my problem:

I have 2 columns that contain around 90% same but 10% different values (column A starts from second row A2, column B starts from second row as well B2).

Now, I need to a script or a function that can:

COMPARE column A with B

In column C, write the CELLS which are in column B but NOT in column A.

For example, column A contains the following data: a1, b1, c1, d1, e1

Column B contains the following data: a1, c1, f1, g1

In this case, the data that needs to be in column C is f1 and g1.

Believe me, I've read a lot on this forum and got some software apps to help me do that but all they do is compare on the basis of rows, if data in 2 columns is different in 2 rows, they mark that as a difference. In the previous example, c1 is in column B (2nd row) and b1 is in column A (2nd row.) b1 and c1 are both in row 2 but that doesn't mean something is different. The same data is contained in one cell (doesn't matter where it's located) in both columns so the script/function should treat it as matching data when comparing.


See More: compare cells

Report •


#1
August 2, 2010 at 10:49:15
Hi,

Put this formula in C2
=IF(ISNA(VLOOKUP(B2,$A$2:$A$6,1,FALSE)),B2,"")
Modify the range A2 to A6 to include all the cells in use in column A - but make sure you keep the $ signs, as shown.

Drag the formula in C2, down alongside all cells in column B that contain values.

Column C will show all values that are in column B but do not appear in column A.

Regards


Report •

#2
August 2, 2010 at 10:59:39
Thanks but here's the problem: There are over 9400 rows in column B lol

Is there a way to make all this without dragging? Thanks.


Report •

#3
August 2, 2010 at 11:04:06
Manually:

Enter this in C2 and drag it down:

=IF(ISNA(MATCH(B2,$A$2:$A$6,0)),B2,"")

Select the results and do an Edit...Copy...Paste Special...Values.

Sort to bring the missing values to the top of the column.

Automagically:

Sub WhatsMissing()
Dim lastA_Row, lastB_Row, c_Row, myVal As Integer
Dim c As Range
'Find length of Column A & B
  lastA_Row = Range("A" & Rows.Count).End(xlUp).Row
  lastB_Row = Range("B" & Rows.Count).End(xlUp).Row
'Initialize Column C row counter
  c_Row = 1
'Loop through Column A looking for values from Column B
   With Range("A2:A" & lastA_Row)
    For myVal = 2 To lastB_Row
     Set c = .Find(Range("B" & myVal), lookat:=xlWhole)
'If not found, put value in Column C
        c_Row = c_Row + 1
        Range("C" & c_Row) = Range("B" & myVal)
      End If
    Next
   End With
End Sub


Report •

Related Solutions

#4
August 2, 2010 at 11:07:17
To avoid the Drag, try this:

Edit...Go To...

Enter B2:B9400 in the Reference field

Enter your formula in B2

Hit Ctrl-Enter to auto-fill the selected range.


Report •

#5
August 2, 2010 at 11:13:49
I tried entering =IF(ISNA(VLOOKUP(B2,$A$2:$A$9990,1,FALSE)),B2,"")

into C2 where I replaced $A$6 with $A$9990 and then pressed F5 (edit-go-to in excel 2007) and excel reported an error. It said that the formula contains an error but didn't say what type of error.

I've also tried the VBA above and it wrote: Compile error: End If without block If


_----------If this helps, here's an example file_-----

http://www.speedyshare.com/files/23...

The only cell different is B27. Now, if you can make a function/script that will automatically output only B27 into C without crashing anything you're a very smart person :D


Report •

#6
August 2, 2010 at 11:16:40
I've also tried the VBA above and it wrote: Compile error: End If without block If

Report •

#7
August 2, 2010 at 11:35:55
Hi,

As to dragging a formula down 10000 rows, it took less than 30 seconds on my laptop - and it isn't a latest or greatest model. Did you try dragging the formula down?

Or:
Select the modified cell C2
Copy
Select C3 and then f5 and enter C9990
Select cell C9990
Scroll back to the top of the worksheet using the scroll bar
Hold down the Shift key and select C3
Paste
All cells C2 to C9990 now contain your formula.

Regards


Report •

#8
August 2, 2010 at 11:50:10
My fault...I typed a comment over a line instead of above it.

Over and Above are not the same thing in this case. :-)

Try this:

Sub WhatsMissing()
Dim lastA_Row, lastB_Row, c_Row, myVal As Integer
Dim c As Range
'Find length of Column A & B
  lastA_Row = Range("A" & Rows.Count).End(xlUp).Row
  lastB_Row = Range("B" & Rows.Count).End(xlUp).Row
'Initialize Column C row counter
  c_Row = 1
'Loop through Column A looking for values from Column B
   With Range("A2:A" & lastA_Row)
    For myVal = 2 To lastB_Row
     Set c = .Find(Range("B" & myVal), lookat:=xlWhole)
'If not found, put value in Column C
      If c Is Nothing Then
        c_Row = c_Row + 1
        Range("C" & c_Row) = Range("B" & myVal)
      End If
    Next
   End With
End Sub


Report •

#9
August 2, 2010 at 12:11:08
I got runtime error '13' type mismatch and when I pressed debug the debugger started from this line:


Set c = .Find(Range("B" & myVal), lookat:=xlWhole)

Btw if you want you can download the file I posted a link above and try testing it so you see what's the prob. in real time.

EDIT: The script works till 421. Then it reaches this page hxxp://yellowpages.superpages.com/supermaps/mapinit.jsp?SRC=comwp&N=pictured+rocks&R=N&STYPE=S&T=Monticello&S=IA&Z=&LID=0011465715&map.x=212&map.y=125&level=8&lat=042208837&lng=-091106668&POI1lat=042208837&POI1lng=-091106668&POI1name=Pictured+Rocks+Methodist+Camp&streetaddress=12004+190th+Street&city=Monticello&state=IA&zip=52310

which seems to be too big and that seems to be the reason why the script gives that error. is there any way to avoid this...


Report •

#10
August 2, 2010 at 12:13:22
Humar, that would be all fine except if I didn't have 10+ files like this I need to process every week or two, so VBA script would be the best thing to have...so everything goes automatically. If you can help out with that, would be very grateful. For now no VBA script currently works.

Report •

#11
August 2, 2010 at 12:18:14
It works for me with the examples you gave.

Try using Range("B" & myVal).Value in both places.


Report •

#12
August 2, 2010 at 12:48:37
How do you mean add to both places? I'm really an excel scriping noob, can you please tell me where to add it? :)

Doesn't work for some reason. If nothing works, we can solve this by shortening the length of a particular cell. Is there a script that will search the content within cells and if it finds that the number of characters is over 147, shorten it to 147 characters? That should solve the problem immediately.

Basically if it finds an URL like:

hxxp://www.web-strategist.com/blog/2010/04/20/matrix-building-and-managing-your-online-career-reputation-unvarnished-linkedin-blogs-and-more/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+WebStrategyByJeremiah+%28Web+Strategy+by+Jeremiah%29&utm_content=Google+Feedfetcher (287 characters)

shorten it to hxxp://www.web-strategist.com/blog/2010/04/20/matrix-building-and-managing-your-online-career-reputation-unvarnished-linkedin-blogs-and-more/?utm (147)

The URL above should prob be counted by Excel as a single word...so it's shortening a word or the starting word of a cell to 147 chars.


Report •

#13
August 2, 2010 at 14:39:23
Hi,

256 characters seems to be the break point - VLOOKUP fails on 256, but works on 255
(tested in Excel 2003)

The following works, but of course it's slow - as it isn't using the Find function.

Sub testit()
Dim rngAstart As Range
Dim rngBstart As Range
Dim rngAend As Range
Dim rngBend As Range
Dim rngAcell As Range
Dim rngBcell As Range
Dim blnNoMatch As Boolean

'set starts
Set rngAstart = Range("A2")
Set rngBstart = Range("B2")
'find ends
Set rngAend = Range("A" & CStr(Application.Rows.Count)).End(xlUp)
Set rngBend = Range("B" & CStr(Application.Rows.Count)).End(xlUp)

'loop through cells in col. B
For Each rngBcell In Range(rngBstart, rngBend)
    'set flag
    blnNoMatch = True
    'loop through cells in col. A
    For Each rngAcell In Range(rngAstart, rngAend)
        If rngBcell.Text = rngAcell.Text Then
            blnNoMatch = False
        End If
        If blnNoMatch = False Then Exit For
    Next rngAcell
'if no match put text in col. C
If blnNoMatch = True Then
    rngBcell.Offset(0, 1).Value = rngBcell.Text
End If
Next rngBcell

End Sub

Regards


Report •

#14
August 2, 2010 at 15:06:42
Thanks but how do I shorten the characters, in the 9000 rows there are a few URLs that are 300+ characters, can't look for each of them individually there must be a way to shorten them via a script.

Report •

#15
August 3, 2010 at 03:03:26
Here's another approach...

If we use VBA arrays, we are not limited by the 256 character issue.

This code loads Columns A and B into VBA arrays and then searches the Column A array for each element in the Column B array. If an element isn't found, it places it in the next empty cell of Column C.

With 10K URL's in Column A and 1K in Column B, it took about 2 minutes. It places the non-matching URL's in sequential cells starting on C2.

Option Explicit
Sub WhatsMissing2()
Dim lastA_Row, lastB_Row, c_Row As Integer
Dim nxtA, nxtB, chkB As Integer
Dim c As Range
Dim URL_A(), URL_B()

'Find length of Column A & B
  lastA_Row = Range("A" & Rows.Count).End(xlUp).Row
  lastB_Row = Range("B" & Rows.Count).End(xlUp).Row
ReDim URL_A(lastA_Row), URL_B(lastB_Row)
'Load URL's from Column A into an array
    For nxtA = 2 To lastA_Row
     URL_A(nxtA) = Range("A" & nxtA)
    Next
'Load URL's from Column B into an array
    For nxtB = 2 To lastB_Row
     URL_B(nxtB) = Range("B" & nxtB)
    Next
'Initialize C Row counter
  c_Row = 1
'Loop through URL_B Array, searching URL_A array
'for each element
 Application.ScreenUpdating = False
    For chkB = 2 To lastB_Row
'if element is not found, put it Column C
     If Not InStr(Join(URL_A()), URL_B(chkB)) > 0 Then
      c_Row = c_Row + 1
      Range("C" & c_Row) = URL_B(chkB)
     End If
    Next
 Application.ScreenUpdating = True

End Sub


Report •

#16
August 3, 2010 at 03:26:46
Thanks, that worked pretty well.

The thing is, I don't need those 256+ character URLs so if there's a function that will remove them or shorten them to 256 characters that would be awesome. But this is already very good.

Thanks a lot!

Btw, it prob requires 1-2 changes, how do you output in D cells that are in A but not in B? Can it be added in the same function or it must be as a separate one.


Report •

#17
August 3, 2010 at 09:06:15
Shortening the URL's is pretty straight forward. Look up the RIGHT, LEFT and MID functions in Excel Help.

However, to get you what you asked for (a list in C of URL's that are in B but not in A) I don't see any advantage of performing that extra step since the VBA Arrays don't care about the length.

If you have some other reason for shortening the URL's, that's a different story.

The code to put the URL's from Column A into column D is pretty straight forward, but I'm going to delay offering that at this time for the following reason:

You've already added an additional requirement (Column D) but in your latest response you said it prob requires 1-2 changes.

What other changes are you looking for? Continually tweaking/adding things to the code is not an efficient use of our time.

If you'll tell us everything you want to the code to do, we may be able to write the final version of the code in one shot and not continually change things based on new requirements.


Report •

#18
August 3, 2010 at 14:30:02
Yes I agree. Shortening wouldn't be necessary, it was just a curiosity on my part.

Here's what I'm looking for:

output cells in column C, the data should be cells that are in column B but not in column A (already done)

output cells in column D, the data should be cells that are in column A but not in column B.

All that combined as a) 1 script that will perform those functions at once b) 2 separate scripts that will perform each function separately

That's it. That's 100% of what I need. Hopefully everything is clear now.


Report •

#19
August 3, 2010 at 16:33:04
Option Explicit
Sub WhatsMissing2()
Dim lastA_Row, lastB_Row
Dim c_Row, d_Row As Integer
Dim nxtA, nxtB, chkA, chkB As Integer
Dim c As Range
Dim URL_A(), URL_B()

'Find length of Column A & B
  lastA_Row = Range("A" & Rows.Count).End(xlUp).Row
  lastB_Row = Range("B" & Rows.Count).End(xlUp).Row
ReDim URL_A(lastA_Row), URL_B(lastB_Row)
'Load URL's from Column A into an array
    For nxtA = 2 To lastA_Row
     URL_A(nxtA) = Range("A" & nxtA)
    Next
'Load URL's from Column B into an array
    For nxtB = 2 To lastB_Row
     URL_B(nxtB) = Range("B" & nxtB)
    Next
Application.ScreenUpdating = False
'Initialize C Row counter
  c_Row = 1
'Place Text String In C1
  Range("C1") = "Links Added"
'Loop through URL_B Array, searching URL_A array
'for each element
    For chkB = 2 To lastB_Row
'If element is not found, put it Column C
     If Not InStr(Join(URL_A()), URL_B(chkB)) > 0 Then
      c_Row = c_Row + 1
      Range("C" & c_Row) = URL_B(chkB)
     End If
    Next
  'Initialize D Row counter
  d_Row = 1
'Place Text String In D1
  Range("D1") = "Links Removed"
  'Loop through URL_A Array, searching URL_B array
'for each element
    For chkA = 2 To lastA_Row
'If element is not found, put it Column D
     If Not InStr(Join(URL_B()), URL_A(chkA)) > 0 Then
      d_Row = d_Row + 1
      Range("D" & d_Row) = URL_A(chkA)
     End If
    Next
 Application.ScreenUpdating = True

End Sub


Report •

#20
August 3, 2010 at 16:38:03
Thank you, Mr. VBA genius.

Report •

#21
August 4, 2010 at 05:27:20
Hi,

If you still want to shorten the text, here is a macro that will shorten all text in columns A and B, limiting text to a maximum 256 characters.

Anything longer than 256 characters is truncated with the first 240 characters, an ellipsis character "..." and the last 15 characters.

Public Sub Shorten()
Dim rngAStart As Range
Dim rngBStart As Range
Dim rngAEnd As Range
Dim rngBEnd As Range
Dim rngCell As Range

'set starts
Set rngAStart = Range("A2")
Set rngBStart = Range("B2")
'find ends
Set rngAEnd = Range("A" & CStr(Application.Rows.Count)).End(xlUp)
Set rngBEnd = Range("B" & CStr(Application.Rows.Count)).End(xlUp)

'loop through column A
For Each rngCell In Range(rngAStart, rngAEnd)
    If Len(rngCell.Text) > 256 Then
        'shorten with an elipsis '...' between the two parts
        rngCell.Value = Left(rngCell, 240) & Chr(133) & Right(rngCell, 15)
    End If
Next rngCell
'loop through column B
For Each rngCell In Range(rngBStart, rngBEnd)
    If Len(rngCell.Text) > 256 Then
        'shorten with an elipsis '...' between the two parts
        rngCell.Value = Left(rngCell, 240) & Chr(133) & Right(rngCell, 15)
    End If
Next rngCell
End Sub

I have only tested this on dummy text, so if you do try it out, please do so on a copy of your data.

Regards


Report •

#22
August 4, 2010 at 09:13:59
Thanks for the help, realized that won't need that ultimately, but may find it useful for the future.

Report •


Ask Question