quickest way to compare ranges

[Luis939]

Im debating the best way to determine whether the strings in numerous cells of one column can be compared with strings in another column.
Column A has cells with strings that contain many blank characters
Column B has basically those same string but without the blank characters
For example:

A B
a b c abc

def ghi defghi

Now it seems there is no function to make such a comparison but what should i do without affecting column A.
Should i temporarily make a copy of column A, apply the substitute function to get rid of the blanks, and then compare. Should I store each range of column A in some sort of data structure and then compare the values of column B with each element of that data structure.
Basically what does anybody recommend I do so that i can efficiently compare and match those 2 columns. Thanks a million!

 

[xlbo]

In col C, enter:
=IF(A1=SUBSTITUTE(B1," ",""),"MATCH","NO MATCH")
and copy down

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

As we are in VBA forum, I would suggestsomething like this:
For Each myCell In Range("A:A").UsedRange
If Replace(myCell.Text," ","") <> Replace(myCell.Offset(0,1).Text," ","") Then
MsgBox "Different"
End If
Next

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

oops - missed that

option base 1
sub GetDiffs()
dim initArr as variant, compArr as variant, lRow as long
lRow = cells(65536,1).end(xlup).row
initArr() = range("A1:A" & lRow)
compArr() = range("B1:B" & lRow)
for i = lbound(initArr()) to ubound(compArr())
 if initArr(i) = replace(cmpArr(i)," ","") then Range("C" & i).value = "MATCH"
next i
end sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Luis939]

No im sorry cause i think ive failed you both on my explanation, i didnt mean to say that these strings will be lined up in the same row i have one column that might have a different number of cells with strings, so i wanted to use the .Find method so that i can say

set foundcell = .find(.....

but i dotn think u can do that becuase technically the strings arent the same..one has blank characters in it, and the other does not....tahts why i suggested maybe creating a copy of the first column so that that column will have the strings without any blanks, and i can effectively use the .find method to find those strings....im not sure if im makin msyelf clear, sorry

 

[xlbo]

Not a problem

Sub FindEm()
dim lRow as long, fWhat as string, fWhere as range, fCell as range
lRow = cells(65536,1).end(xlup).row
[COLOR=green]'Set range to look up values in[/color]
set fWhere = range("B1:B" & lRow)
[COLOR=green]'start loop[/color]
For each cel in range("A1:A" & lRow)
  [COLOR=green]'Set variable = cell text without spaces[/color]
  fWhat = replace(cel.text," ","")
[COLOR=green]'Do the find[/color]
   set fCell = fWhere.find(fWhat, lookin:=xlvalues, lookat:=xlwhole)
    if not fCell is nothing then
       [COLOR=green]'Match found[/color]
    else
      [COLOR=green]'Put marker against row with no match[/color]
      cel.offset(0,2).value = "NO MATCH"
    end if
next
end sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Luis939]

sort of close (just the opposite of what i need) but i want to take the values of column B and search them in column A, so what i need to do is take each cell of column B, use the replace function on it and then look for them in column A, but I can't change column A. Therefore, it doesnt seem possible for me to compare something like "ABC" (from column B) and find a match with "A B C" (from column A).
Even though it might seem like the least desirable way, i think ill just make a copy of colunm A and use the Replace function on that copy so that i can effectively find a match