Thanks Don,
Not having much experiance with using INDEX and MAtch required a little more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value = Application.WorksheetFunction.Index(ProjAry, _
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine with index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into one.
I have used VLOOKUP to retrieve the majority of the required data.
The problem I am running into is when there are multiple entries for the
same person.
Now there are data entry issues that if resolved could eleviate the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the name in
Column A (ie. Josh Smith is always at PHX). What I was hoping to do was
have
VLOOKUP compare the values in Column B for each entry in Column A and
transfer the value in Column C to Worksheet A. Is this possible?

Thanks,
Matt

Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction) is
generating a compile error "Sub or Function not defined". I guess I'm not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a little more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value = Application.WorksheetFunction.Index(ProjAry, _
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine with index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into one.
I have used VLOOKUP to retrieve the majority of the required data.
The problem I am running into is when there are multiple entries for the
same person.
Now there are data entry issues that if resolved could eleviate the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the name in
Column A (ie. Josh Smith is always at PHX). What I was hoping to do was
have
VLOOKUP compare the values in Column B for each entry in Column A and
transfer the value in Column C to Worksheet A. Is this possible?

Thanks,
Matt

Anytime you use a function you need to use application
ie application.match
application.index
I don't think you will need to use application.worksheetfunction.
application alone should do it.

Without looking at this in depth, you could probably avoid using the
functions if you used
vba FIND and OFFSET

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction) is
generating a compile error "Sub or Function not defined". I guess I'm
not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a little
more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value = Application.WorksheetFunction.Index(ProjAry,
_
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine with
index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into one.
I have used VLOOKUP to retrieve the majority of the required data.
The problem I am running into is when there are multiple entries for
the
same person.
Now there are data entry issues that if resolved could eleviate the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the
name in
Column A (ie. Josh Smith is always at PHX). What I was hoping to do
was
have
VLOOKUP compare the values in Column B for each entry in Column A
and
transfer the value in Column C to Worksheet A. Is this possible?

Thanks,
Matt

Thanks again Don.
I will look into using FIND and Offset instead.

Matt

"Don Guillett" wrote:

Anytime you use a function you need to use application
ie application.match
application.index
I don't think you will need to use application.worksheetfunction.
application alone should do it.

Without looking at this in depth, you could probably avoid using the
functions if you used
vba FIND and OFFSET

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction) is
generating a compile error "Sub or Function not defined". I guess I'm
not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a little
more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value = Application.WorksheetFunction.Index(ProjAry,
_
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine with
index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into one.
I have used VLOOKUP to retrieve the majority of the required data.
The problem I am running into is when there are multiple entries for
the
same person.
Now there are data entry issues that if resolved could eleviate the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the
name in
Column A (ie. Josh Smith is always at PHX). What I was hoping to do
was
have
VLOOKUP compare the values in Column B for each entry in Column A
and
transfer the value in Column C to Worksheet A. Is this possible?

Thanks,
Matt

let us know how it worked out

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Thanks again Don.
I will look into using FIND and Offset instead.

Matt

"Don Guillett" wrote:

Anytime you use a function you need to use application
ie application.match
application.index
I don't think you will need to use application.worksheetfunction.
application alone should do it.

Without looking at this in depth, you could probably avoid using the
functions if you used
vba FIND and OFFSET

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction)
is
generating a compile error "Sub or Function not defined". I guess
I'm
not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a
little
more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value =
Application.WorksheetFunction.Index(ProjAry,
_
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine
with
index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into
one.
I have used VLOOKUP to retrieve the majority of the required
data.
The problem I am running into is when there are multiple entries
for
the
same person.
Now there are data entry issues that if resolved could eleviate
the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the
name in
Column A (ie. Josh Smith is always at PHX). What I was hoping
to do
was
have
VLOOKUP compare the values in Column B for each entry in Column
A
and
transfer the value in Column C to Worksheet A. Is this
possible?

Thanks,
Matt

Don, I think I finally got it. Just one more question. Even to a noob like
me the code I developed looks cumbersome. Is there a way to streamline it?

Dim ProjAry As Range, SrcChk1 As Range
Dim SrcFnd1 As String, SrcFnd2 As String, DestChk1 As String
Dim DestChk2 As String

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")

Sheets("Projections").Range("G2").Select
Do
DestChk1 = ActiveCell.Offset(0, -5).Value
DestChk2 = ActiveCell.Offset(0, -2).Value
Set SrcChk1 = ProjAry.Find(What:=DestChk1, LookAt:=xlWhole, _
SearchOrder:=xlByColumns)

If Not SrcChk1 Is Nothing Then
SrcFnd1 = SrcChk1.Offset(0, 2).Value

If DestChk2 = SrcFnd1 Then
SrcFnd2 = SrcChk1.Offset(0, 5).Value
ActiveCell.Value = SrcFnd2
End If

Else
ActiveCell.Value = "0"
End If

If IsEmpty(ActiveCell) Then
ActiveCell.Value = "0"
End If

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))

Raw Projections contains the data I need
Projections contains the two values (DestChk) I use to look up the value I
need (SrcFnd)
If both DestChk are true then the value is returned otherwise 0 is entered

Thanks again,
Matt

"Don Guillett" wrote:

let us know how it worked out

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Thanks again Don.
I will look into using FIND and Offset instead.

Matt

"Don Guillett" wrote:

Anytime you use a function you need to use application
ie application.match
application.index
I don't think you will need to use application.worksheetfunction.
application alone should do it.

Without looking at this in depth, you could probably avoid using the
functions if you used
vba FIND and OFFSET

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction)
is
generating a compile error "Sub or Function not defined". I guess
I'm
not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a
little
more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value =
Application.WorksheetFunction.Index(ProjAry,
_
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine
with
index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into
one.
I have used VLOOKUP to retrieve the majority of the required
data.
The problem I am running into is when there are multiple entries
for
the
same person.
Now there are data entry issues that if resolved could eleviate
the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the
name in
Column A (ie. Josh Smith is always at PHX). What I was hoping
to do
was
have
VLOOKUP compare the values in Column B for each entry in Column
A
and
transfer the value in Column C to Worksheet A. Is this
possible?

Thanks,
Matt