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
--
Linda

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" wrote:

> 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
> --
> Linda

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


--
Linda


"Joel" wrote:

> 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" wrote:
>
> > 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
> > --
> > Linda

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 wrote:
>
> 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
> --
> Linda

--

Dave Peterson

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.
--
Linda


"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
> > --
> > Linda
>
> --
>
> Dave Peterson
>

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
--
Linda


"Joel" wrote:

> 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" wrote:
>
> > 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
> > --
> > Linda

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

Hi Linda,

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

Wkr,

JP

"mathel" <(E-Mail Removed)> wrote in message
news:F4666F32-2ED5-4E65-AD85-(E-Mail Removed)...
>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.
> --
> Linda
>
>
> "Dave Peterson" wrote:
>
>> 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 wrote:
>> >
>> > 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
>> > --
>> > Linda
>>
>> --
>>
>> Dave Peterson
>>

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!
--
Linda


"arjen van..." wrote:

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

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!
--
Linda


"JP Ronse" wrote:

> Hi Linda,
>
> Is Edit/Replace find:<space> replace:<nothing> not the easiest way to do?
>
> Wkr,
>
> JP
>
> "mathel" <(E-Mail Removed)> wrote in message
> news:F4666F32-2ED5-4E65-AD85-(E-Mail Removed)...
> >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.
> > --
> > Linda
> >
> >
> > "Dave Peterson" wrote:
> >
> >> 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 wrote:
> >> >
> >> > 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
> >> > --
> >> > Linda
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>