Spreadsheet 1 has A = CusPN
Spreadsheet 2 has A= CusPN and B= MfgPN

One CusPN represents up to 20 different MfgPN, thus when I do a Vlookup I
only get the first MfgPN from Spreadsheet 2. I need to return all results.
I am guessing I can use a table array and set up as a macro for everyday use.
Can someone help? The worksheet is 7 Columns wide total (A-G). I know how
to set this up as a macro and have tried to make this work but have had no
luck. PLEASE help! My whole spreadsheet is 30K lines long and I have been
doing this weekly manually. My eyes are bleeding... Ha Ha
Thank you,
Lisa

Lisa
You need to provide more detail about the layout of what you have and
the layout of what you want to happen. You say sheet 1 has A=CusPN. Is
this column A? And is "CusPN" the header for column A?
You say sheet 2 has the CusPN (column header?) and B (column B?) has MfgPN
and that MfgPNs are about 20 to 1.
You say that you want to return all the MfgPN for one CusPN. Where do you
want this?
If I have guessed right about what you want, it's simple to do with a macro.
Post back with more layout detail. HTH Otto
"lmarquis" <u23027@uwe wrote in message news:61c696989b89f@uwe...
Spreadsheet 1 has A = CusPN
Spreadsheet 2 has A= CusPN and B= MfgPN

One CusPN represents up to 20 different MfgPN, thus when I do a Vlookup I
only get the first MfgPN from Spreadsheet 2. I need to return all
results.
I am guessing I can use a table array and set up as a macro for everyday
use.
Can someone help? The worksheet is 7 Columns wide total (A-G). I know
how
to set this up as a macro and have tried to make this work but have had no
luck. PLEASE help! My whole spreadsheet is 30K lines long and I have
been
doing this weekly manually. My eyes are bleeding... Ha Ha
Thank you,
Lisa

Lisa,

The "vlookup" function can't return more than one value because a cell can't
hold more than one value. I would suggest writing a macro using the Find
method.

Here's an example of code I frequently use to accomplish this:

With ActiveSheet.Range("a1:a65536")
Set c = .Find(gathNum, LookIn:=xlValues) ' Search within top row
If Not c Is Nothing Then
findFirstRow = c.row
Do
findMatchRow = c.row
If (something) Then
do something (like copy the value to an array or another
spreadsheet)
Else
Set c = .findNext(c)
End If
Loop Until (c Is Nothing Or c.row = findFirstRow)
End If
End With

Be very careful to set up your conditions correctly, or you will cause an
infinite loop. (If you do, use, Ctrl+BreaK to stop the code.) Read the help
files for more info.

Good luck,
Pflugs

"lmarquis" wrote:

Spreadsheet 1 has A = CusPN
Spreadsheet 2 has A= CusPN and B= MfgPN

One CusPN represents up to 20 different MfgPN, thus when I do a Vlookup I
only get the first MfgPN from Spreadsheet 2. I need to return all results.
I am guessing I can use a table array and set up as a macro for everyday use.
Can someone help? The worksheet is 7 Columns wide total (A-G). I know how
to set this up as a macro and have tried to make this work but have had no
luck. PLEASE help! My whole spreadsheet is 30K lines long and I have been
doing this weekly manually. My eyes are bleeding... Ha Ha
Thank you,
Lisa