Is there a macro for returning multiple values through a Vlookup?

[anshu minocha]

Foe eg: Sheet2 contains:

Project# Phase Sp# Details status Manager ...
1234 1-0110 N80 abc Test am
1234 1-0210 N97 def Incom bd
1234 2-0210 N45 lmn Test cf
1234 1-0110 N67 jkl Com er


Sheet1 contains:

Project# Phase SP# Status Manager
1234 1-0110

The above 2 values are inserted by the user:
Now: On running the macro:

the sheet 1 should be populated with all the records for phase 1-0110
Desired output:

Project# Phase Sp# Status Manager
1234 1-0110 N80 Test am
N65 Com er

Any help would be greatly appreciated!!!
Thanks

 

[JP]

How about a PivotTable or Advanced Filter instead?

--JP

 

[anshu minocha]

Hey JP,

But the requirement is to obtain the values on a single
click.So was thing of writing a macro:
If atleast the macro can Populate all the SP# for the particular phase

Sheet1:

Project# Phase SP#
1234 1-0110 N80
N65
Is it possible to get all the SPId's getting populated for phase
1-0110 from sheet2 using a macro
Please advise
Thanks

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[JP]

You can create PivotTables and Advanced Filters using VBA. If only you
would do a search to find out how to use those tools.

You could also put the data into Access and create a parameter query
that asks you for the Project# and Phase, and returns a table with the
matching records.

--JP

 

[L. Howard Kittle]

Hi Anshu,

Give this a test, you will have to change the ranges in the code to suit
your sheet or set up an example on your sheet to match the code ranges.
Can be tweeked to suit of course.

Sub TheProjects()
Dim i As Long
Dim j As Variant
Dim Pro As Range
Dim Pha As Range
Dim cell As Range

i = Range("I9").Value ' Type Project number in I9
j = Range("J9").Value ' Type Phase number in J9
Set Pro = Range("B9:B12") ' List of Project# range
Set Pha = Range("C9:C12") ' List of Phase numbers range

'Copies the Pro and Pha number into the next available row in Column I and J
Range("I9:J9").Copy Range("K100").End(xlUp).Offset(1, -2).Resize(1, 2)

'Gathers the info for Pro range and Pha range and copies to the
'next available row in column K
For Each cell In Pro
If cell.Value = i And cell.Offset(0, 1).Value = j Then
cell.Offset(0, 2).Resize(1, 4).Copy
Range("K100").End(xlUp).Offset(1, 0)
End If
Next
End Sub

HTH
Regards,
Howard

 

[Don Guillett]

You did not follow my instructions
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[anshu minocha]

Hello Don,
I did send it on your gmail id.
Thanks

 

[Don Guillett]

I did NOT get it and if you can't do it in the manner I request, I will not
be able to assist. Follow these instructions.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[anshu minocha]

Thankyou I happened to figure out the problem.
Thanks JP and Don

 

[anshu minocha]

Thankyou Howard