Using a vlookup to search multiple criteria for one result?

[srm6]

HI all!! Thank you for time and knowledge. I am looking for a vlooku
to search multiple criteria for one result?

Below is my workbook with 2 sheets. The "Name" column in "Sheet 1" i
blank. This is where I want to put the vlookup formula. The "Answe
should be:" doesn't exist. It just for reference to check the results.


I am looking for a vlookup formula that will lookup the "Location Code
and the "Task" in "Sheet 1" and compare it to "Sheet 2" to give me th
result from "Sheet 2". I am looking for the name of the Location/Task.
The only unique value is the Task (the location can be duplicated man
times and the different location codes can have the same task as othe
location codes).

Sheet 1
Name Location Code Task Answer should be:
{Formula Here} IL001 010 Bob
{Formula Here} IL001 009 Bill
{Formula Here} IL002 008 Diane
{Formula Here} IL002 007 Tina
{Formula Here} IL003 006 Bill
{Formula Here} IL004 005 Tina
{Formula Here} IL004 004 Bob
{Formula Here} IL005 003 Diane
{Formula Here} IL006 002 Tina
{Formula Here} IL007 001 Bob
{Formula Here} IL008 000 Diane


Sheet 2
Location Code Task Name
IL008 000 Diane
IL007 001 Bob
IL006 002 Tina
IL005 003 Diane
IL004 004 Bob
IL004 005 Tina
IL003 006 Bill
IL002 007 Tina
IL002 008 Diane
IL001 009 Bill
IL001 010 Bob

I have tried many different options to no avail. One simple option
thought for sure would work is: =VLOOKUP(B2&C2,name,3,FALSE)
{I named th
range "name"}

I have also attached the information.

Thank you so much for your time and I look forward to seeing th
results

 

[Claus Busch]

hi,

Am Mon, 11 Mar 2013 21:10:34 +0000 schrieb srm6:

Sheet 1
Name Location Code Task Answer should be:
{Formula Here} IL001 010 Bob
{Formula Here} IL001 009 Bill
{Formula Here} IL002 008 Diane
{Formula Here} IL002 007 Tina
{Formula Here} IL003 006 Bill
{Formula Here} IL004 005 Tina
{Formula Here} IL004 004 Bob
{Formula Here} IL005 003 Diane
{Formula Here} IL006 002 Tina
{Formula Here} IL007 001 Bob
{Formula Here} IL008 000 Diane

Sheet 2
Location Code Task Name
IL008 000 Diane
IL007 001 Bob
IL006 002 Tina
IL005 003 Diane
IL004 004 Bob
IL004 005 Tina
IL003 006 Bill
IL002 007 Tina
IL002 008 Diane
IL001 009 Bill
IL001 010 Bob

try:
=INDEX(Sheet2!$C$2:$C$200,MATCH(B2&C2,Sheet2!$A$2:$A$200&Sheet2!$B$2:$B$200,0))
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch

 

[Kevin@Radstock]

Hi srm

You want to invert your data

Assuming data is on Sheet2 A1:C12 including column headers
On sheet1 in A2 copy across to C2 & down to C12

=INDEX(Sheet2!$A$2:$C$12,ROWS(2:$12),COLUMNS($A:A)

Kevi

 

[srm6]

hi,

Am Mon, 11 Mar 2013 21:10:34 +0000 schrieb srm6:
-

try:
=INDEX(Sheet2!$C$2:$C$200,MATCH(B2&C2,Sheet2!$A$2:$A$200&Sheet2!$B$2:$B$200,0))
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch

Thank you Claus Busch. This worked perfectly. However, when I put i
into my spreadsheet consisting of 19804 rows it bogged down the repor
and took forever for me to run the existing macro I have in the report.
Is there a way to add this to an existing macro so it will run faster?

My existing macro is as follows:

Sub CopyData3()
Application.ScreenUpdating = False
Dim vNames, vSheets, lLastRow&, n&, c As Range
Const sNames$ = "brad,casey,dave,dott,jason,jesus,rick,russ"
Const sSheets$ = "brad,casey,dave,dott,jason,jesus,rick,russ"
vNames = Split(sNames, ","): vSheets = Split(sSheets, ",")
lLastRow = Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Raw Data")
For n = LBound(vNames) To UBound(vNames)
For Each c In .Range("A2:A" & lLastRow)
If c = vNames(n) And c.Offset(0, 4) <= Date _
And c.Offset(0, 6) = "" Then
.Range(Cells(c.Row, 1), Cells(c.Row, 5)).Copy _
Sheets(vSheets(n)).Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
End If 'c = vNames(n)
Next 'c
Next 'n
End With 'Sheets("Raw Data")
Application.ScreenUpdating = True
End Sub 'CopyData3

The macro is taking my raw data and splitting the information up int
seperate sheets based on the date and the user name.

The formula will only exist in the raw data. The copydata macro wil
then use that information to determine what data goes into which sheet.


Eventually I would like the macro to create the sheet for each user bu
for now this one works. I just need to get that formula into the macro
Thanks for your help

 

[GS]

Perhaps you just want to copy the 'Value's!, perhaps!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

What's making it takes so long is that it reads/writes directly to the
worksheet. This will always be slower than dumping the range into
an array and process in memory first, then dump the data into the
target worksheet.

In this case each name's data would have to be put into a temporary
array and dumped to the respective sheet in turn. This would be a bit
more than trivial and so will take some time...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi,

Am Tue, 12 Mar 2013 16:15:53 +0000 schrieb srm6:

This worked perfectly. However, when I put it
into my spreadsheet consisting of 19804 rows it bogged down the report
and took forever for me to run the existing macro I have in the report.
Is there a way to add this to an existing macro so it will run faster?

that is the disadvantage of array formulas in more than 500 rows.
Try it with VBA:

Sub Test()
Dim LRow As Long
Dim i As Long
Dim rngC As Range
Dim firstAddress As String

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 2 To LRow
Set rngC = Sheets("Sheet2").Range("A1:A20000").Find _
(.Cells(i, 2), LookIn:=xlValues)
firstAddress = rngC.Address
Do
If rngC.Offset(0, 1) = .Cells(i, 3) Then
.Cells(i, 1) = rngC.Offset(0, 2)
End If
Set rngC = Sheets("Sheet2").Range("A1:A20000").FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> firstAddress
Next
End With
End Sub


Regards
Claus Busch

 

[srm6]

This worked perfectly. We are all very happy with the results. Than
you so much for your time and effort on this matter. It is greatl
appreciated.