Compare columns

[mathel]

I've checked thru the Discussion group and found this subject has been asked
several times, but can't find something I can use.

I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and
'Transactions'. I need to compare Column A in ws 'Transactions' to Column a
in ws 'Exposed'. If there is a match, have the data show on the same row in
Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere
from 500 rows to as many as 5000. I know the simple answer is to use
VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and
for whatever reason the formula does NOT always work and the data is being
missed so I am looking for VBA.

Even if the data in Column A could not be put into Column B, I would be
happy if the cell was highlighted.

Thanks

 

[Joel]

If VLOOKUP isn't working I don't think VBA will be any better. There must be
something different with the data for VLOOKUP not to work. I would check the
Tools - options - Calculation menu to see what the number of Iterations is
set to. If the iteration n umber is set too low or you are set to manual
calculation this may be the cause of the problem.

 

[mathel]

If the Interation is different on different PCs, could it be a factor? IE: I
verified the Options on the PC I'm using in the office, it shows Maximum 100,
I would have to check my PC that I use from home to see if there is a
difference.

On the other hand, there is 1 row I know is in both columns, and while
VLookUp did not find the row, I found the following piece of VB that 'showed'
the data. However, this code changes the text, rather than highlighting the
cell. Unfortunately I know nothing about VBA, so don't know how to modify it
so it would work to hightlight only - This example compares Column A to B in
the same worksheet:

Sub Replace_TExt()


For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) <> "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

 

[Dave Peterson]

I don't see how using VBA would help find an exact match if you can't do it
using formulas.

I'd look for differences in the data.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????

 

[mathel]

I found the problem! The data we are adding to worksheet 'Exposed' on a
daily basis is a 20 digit number, so the column in the worksheet is formatted
to 'Text'. The data is being copied from a Word document, and we use
'Paste-Special-Text' into the spreadsheet. What I found was that if I double
click the number in the Word doc, there is a space at the end of it On all
numbers where there is a space at the end, VLOOKUP will not do a match.

That being said, I did find a piece of code that found the number whether it
had a space at the end of it or not. The problem is, this code is to replace
the text in the found row. I do not know anything about VBA, so don't know
how to modify it so that it would, lets say, change the cell interior to
Yellow. The code is as follows:

Sub Replace_TExt()


For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) <> "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

Is it possible to have this code 'modified' to change the cell vs replace
text?

THANK YOU so much for all you help/comments in finding what the problem was
with my ws.

 

[mathel]

After reading both your comments and Dave Peterson's, I found the problem
with my data. When adding more data to the WS, it is copied from a Word doc.
Some people (I think me mostly), double click the data to be copied, vs
dragging across the info to hightlight & copy. The double click method will
include a 'space' at the end of the data. VLOOKUP will not match/find any of
the rows where the data has a space at the end.

Any work-around in the formula, or different formula that can be used that
will recognize the data with or without the space? The formula now used is:
=VLOOKUP(A1,'Daily Transactions'!$A$:$A$19801,1,FALSE)

The type of data I am using is a 20 digit number, the ws has the column(s)
formatted as Text, and when copied from Word to the ws, we us
Paste-special-text.

Thanks

 

[arjen van...]

I'm not sure if this might be what you're looking for, but you can also
consider matching two arrays with each array being based on your two
spreadsheet ranges. Compare each element of the first array to each element
of the second array, and if it matches put the value in the spreadsheet.

Option Explicit

Sub MatchArrays()

Dim arrExposed, arrTransactions As Variant

With Sheets("Exposed")
arrExposed = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value
End With

With Sheets("Transactions")
arrTransactions = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value
End With

Dim i, j As Long

For i = LBound(arrExposed, 1) To UBound(arrExposed, 1)
For j = LBound(arrTransactions, 1) To UBound(arrTransactions, 1)
If arrExposed(i, 1) = arrTransactions(j, 1) Then
Debug.Print (arrExposed(i, 1))
Sheets("Exposed").Cells(i, 2).Value = arrExposed(i, 1)
End If
Next
Next

End Sub

 

[JP Ronse]

Hi Linda,

Is Edit/Replace find:<space> replace:<nothing> not the easiest way to do?

Wkr,

JP

I found the problem! The data we are adding to worksheet 'Exposed' on a
daily basis is a 20 digit number, so the column in the worksheet is
formatted
to 'Text'. The data is being copied from a Word document, and we use
'Paste-Special-Text' into the spreadsheet. What I found was that if I
double
click the number in the Word doc, there is a space at the end of it On
all
numbers where there is a space at the end, VLOOKUP will not do a match.

That being said, I did find a piece of code that found the number whether
it
had a space at the end of it or not. The problem is, this code is to
replace
the text in the found row. I do not know anything about VBA, so don't
know
how to modify it so that it would, lets say, change the cell interior to
Yellow. The code is as follows:

Sub Replace_TExt()


For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) <> "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

Is it possible to have this code 'modified' to change the cell vs replace
text?

THANK YOU so much for all you help/comments in finding what the problem
was
with my ws.

 

[mathel]

I ran this, unfortunately, for some reason it did not work, none of the rows
showed as a match.

However, based on some other comments, I did find the problem with the data
in the worksheet that VLookup wouldn't find a match, didn't realize it was so
sensative. IE, some data had a space at the end of it (which would cause a
#N/A result), whereas if it was exact, no problem, it matched it.

I'm trying to find a workaround to the formula now to find a match whether
there is a space at the end of it or not.

THANK YOU FOR YOUR HELP!

 

[mathel]

Yep, done this, and it resolved the problem. I guess we will have to do this
on a daily basis to ensure none of the users pasted data with a space
included at the end.

Thanks again for all your help. This website has to be one of the best
sources of information and help the I have used to date!

 

[JP Ronse]

Hi Arjen,

This is also a possible solution but in my opinion it will take more time to
go through the routine.

Suppose you have twice a range of 5000 entries and the half is matching,
means that you have to go through the second array for 2500 times.
This results already in 5000 x 2500 = 12.500.000 comparisons to go to
through the second array because no match can be found.

The other 2500 will statistical take 2500 x 2500 compares (6.250.000)

In my first reply to this request, I've attached a zipped workbook with the
use of a collection. Have a look and please let me know if this was of any
help. To be honest, I've ran your code on my model and your code takes about
3 seconds, which is fast.

The collection sub only needs 1 second. If you want to know more about
collections, I can recommend reading Dermot Balson pages:
http://www.westnet.net.au/balson/ModellingExcel/

Wkr,

JP

 

[Dave Peterson]

If all you're missing is that additional trailing space character, maybe you
could change your =vlookup() formula:

=vlookup(a1&" ",sheet2!a:b,2,false)

I hate "fixing" the problem in the formula, though. I would never remember to
"fix" it all the other formulas.

Maybe you could insert a new column and use a worksheet function like:

=trim(a1)

to remove the trailing space.

Or in code:

Dim myRng as range
dim myCell as range
dim wks as worksheet

set wks = activesheet

with wks
set myrng = .range("b1",.cells(.rows.count,"B").end(xlup))
end with

myrng.numberformat = "@" 'text
for each mycell in myrng.cells
mycell.value = trim(mycell.value)
next mycell

===============

Doing the edit|replace (even in code) will make those 20 digit text numbers into
real numbers--and excel only keeps track of 15 significant digits. You may find
that you have 5 zeros at the end of your number.

I found the problem! The data we are adding to worksheet 'Exposed' on a
daily basis is a 20 digit number, so the column in the worksheet is formatted
to 'Text'. The data is being copied from a Word document, and we use
'Paste-Special-Text' into the spreadsheet. What I found was that if I double
click the number in the Word doc, there is a space at the end of it On all
numbers where there is a space at the end, VLOOKUP will not do a match.

That being said, I did find a piece of code that found the number whether it
had a space at the end of it or not. The problem is, this code is to replace
the text in the found row. I do not know anything about VBA, so don't know
how to modify it so that it would, lets say, change the cell interior to
Yellow. The code is as follows:

Sub Replace_TExt()

For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) <> "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

Is it possible to have this code 'modified' to change the cell vs replace
text?

THANK YOU so much for all you help/comments in finding what the problem was
with my ws.

 

[mathel]

Sorry, I thought the 'Find & Replace' resolved the problem, it did remove the
space, but the data in the rows (which are 20 digit numbers) reverted to
something like 5.8834E+18 (on the Edit line, the last 4 digits are all zeros,
so unfortunately, the problem is not yet resolved.

--
Linda

Yep, done this, and it resolved the problem. I guess we will have to do this
on a daily basis to ensure none of the users pasted data with a space
included at the end.

Thanks again for all your help. This website has to be one of the best
sources of information and help the I have used to date!

 

[JP Ronse]

Hi Linda,

I know this 'dam...' thing of unstructured data .

Are there 1 or more spaces after the 'value'?

If you don't mind to send me the file, I'll look further into.

(e-mail address removed)

Believing that I can handle this in the first reply.

Wkr,

JP

 

[arjen van...]

Hi JP,

Thanks for the tip. There's some good material there.

 

[mathel]

Hi Dave, you were right, removing the trailing space reverted the data back
to a number (like 5.88834E+18) making my workbook almost unusable. I tried
both formulas you suggested, =trim(a1), does nothing and I have no idea why,
the formula actually shows exactly as input as if it was text.

The 1st formula "=vlookup(a1&" ",sheet2!a:b,2,false)", unfortunatly does
not work either. It will not match any data, whether there is a space or not.

I'm still trying to find a work-around the problem!

 

[JP Ronse]

Glad you appreciate

 

[Dave Peterson]

My bet is that they're not really trailing spaces...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Then you can use this kind of formula:
=substitute(a1,char(##),"")

Hi Dave, you were right, removing the trailing space reverted the data back
to a number (like 5.88834E+18) making my workbook almost unusable. I tried
both formulas you suggested, =trim(a1), does nothing and I have no idea why,
the formula actually shows exactly as input as if it was text.

The 1st formula "=vlookup(a1&" ",sheet2!a:b,2,false)", unfortunatly does
not work either. It will not match any data, whether there is a space or not.

I'm still trying to find a work-around the problem!

 

[JP Ronse]

Hi Linda,

Based on the latest feedback...

In transactions, following sample data:

Row 1: 1
Row 2: 2
Row 3: =3 & " " & CHAR(9)
Row 4: 4
....
Row 7: =7 & " " & CHAR(10)
....

In Exposed:
Row 1: 1
Row 2: 2
Row 3: 3
....
Row 9: =9 & " " & CHAR(12)

The macro CompareData gives in column B
1 1
2
3 3
4
5 5
6
7 7

8
9 9
10


It has also found the 'duplicates' for 3, 7 and 9.

Wkr,

JP

Sub CompareData()
Dim colTransactions As Collection
Dim varExposed As Variant
Dim lngRow As Long
Dim lngCount As Long
''' read the data
Dim Timer As Date
Dim varRes As Variant

Timer = Now()

Application.ScreenUpdating = False

Set colTransactions = New Collection

With Sheets("Transactions")
.Activate
lngRow = .Cells(1, 1).CurrentRegion.Rows.Count
varExposed = .Range(Cells(1, 1), Cells(lngRow, 1))

For lngCount = 1 To lngRow
colTransactions.Add Item:=varExposed(lngCount, 1),
Key:=Trim(Application.Clean(CStr(varExposed(lngCount, 1))))
Next lngCount
End With

With Sheets("Exposed")
.Activate
.Columns("B").ClearContents
lngRow = .Cells(1, 1).CurrentRegion.Rows.Count
varExposed = .Range(Cells(1, 1), Cells(lngRow, 1))
End With

On Error GoTo Eror_CompareData
For lngCount = 1 To lngRow
varRes =
colTransactions(Trim(Application.Clean(CStr(varExposed(lngCount, 1)))))
''' value iin Exposed found in Tranasactions
Cells(lngCount, 2) =
colTransactions(Trim(Application.Clean(CStr(varExposed(lngCount, 1)))))
Next_Exposed:
Next lngCount

Exit_CompareData:
Debug.Print Now() - Timer
Exit Sub
Eror_CompareData:

Resume Next_Exposed
End Sub