Using VLookup and Conditional Formatting

I am currently working on a project with the company I am working for and we
are having some difficulty with an excel formula. We're basically working out
of two different spreadsheets in a workbook and the only common ground
between the two is the column for part numbers. We used VLookup to pull data
from the first spreadsheet into the second with some conditional formatting.
The vlookup was based on part number.

Here is the problem. Because it involves data from four of our different
plants, some of the part numbers are listed twice, but contain completely
different cost, supplier and buyer information that we need to capture. I
suppose that ideally, is there some sort of function that can tell excel to
only pull the data from one entry of the part number once before trying to
find a second and pulling data like that until it cannot find another entry
and thus, end?

Thanks everyone for all the help! It is greatly appreciated as always!

Hi,

Please post an example so that the problem is clear.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Andy B." wrote in message
...
I am currently working on a project with the company I am working for and
we
are having some difficulty with an excel formula. We're basically working
out
of two different spreadsheets in a workbook and the only common ground
between the two is the column for part numbers. We used VLookup to pull
data
from the first spreadsheet into the second with some conditional
formatting.
The vlookup was based on part number.

Here is the problem. Because it involves data from four of our different
plants, some of the part numbers are listed twice, but contain completely
different cost, supplier and buyer information that we need to capture. I
suppose that ideally, is there some sort of function that can tell excel
to
only pull the data from one entry of the part number once before trying to
find a second and pulling data like that until it cannot find another
entry
and thus, end?

Thanks everyone for all the help! It is greatly appreciated as always!

I'm afraid you can't solve the problem without adding a plant column to both
worksheets and use concatenation of plant identifier and part number as
search key in VLOOKUP.

Regards,
Stefi

„Andy B.â€� ezt Ã*rta:

I am currently working on a project with the company I am working for and we
are having some difficulty with an excel formula. We're basically working out
of two different spreadsheets in a workbook and the only common ground
between the two is the column for part numbers. We used VLookup to pull data
from the first spreadsheet into the second with some conditional formatting.
The vlookup was based on part number.

Here is the problem. Because it involves data from four of our different
plants, some of the part numbers are listed twice, but contain completely
different cost, supplier and buyer information that we need to capture. I
suppose that ideally, is there some sort of function that can tell excel to
only pull the data from one entry of the part number once before trying to
find a second and pulling data like that until it cannot find another entry
and thus, end?

Thanks everyone for all the help! It is greatly appreciated as always!

The formula I am using currently is =IF(ISNA(VLOOKUP($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0)),"",IF(VLOOKU P($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0)="","",VLOOKUP ($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0))).



"Andy B." wrote:

I am currently working on a project with the company I am working for and we
are having some difficulty with an excel formula. We're basically working out
of two different spreadsheets in a workbook and the only common ground
between the two is the column for part numbers. We used VLookup to pull data
from the first spreadsheet into the second with some conditional formatting.
The vlookup was based on part number.

Here is the problem. Because it involves data from four of our different
plants, some of the part numbers are listed twice, but contain completely
different cost, supplier and buyer information that we need to capture. I
suppose that ideally, is there some sort of function that can tell excel to
only pull the data from one entry of the part number once before trying to
find a second and pulling data like that until it cannot find another entry
and thus, end?

Thanks everyone for all the help! It is greatly appreciated as always!

You should post the exact structure of your sheets!
Stefi


„Andy B.â€� ezt Ã*rta:

The formula I am using currently is =IF(ISNA(VLOOKUP($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0)),"",IF(VLOOKU P($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0)="","",VLOOKUP ($A2,'Pull Part
Candidates'!$A:$R,COLUMNS($A2:C2),0))).



"Andy B." wrote:

I am currently working on a project with the company I am working for and we
are having some difficulty with an excel formula. We're basically working out
of two different spreadsheets in a workbook and the only common ground
between the two is the column for part numbers. We used VLookup to pull data
from the first spreadsheet into the second with some conditional formatting.
The vlookup was based on part number.

Here is the problem. Because it involves data from four of our different
plants, some of the part numbers are listed twice, but contain completely
different cost, supplier and buyer information that we need to capture. I
suppose that ideally, is there some sort of function that can tell excel to
only pull the data from one entry of the part number once before trying to
find a second and pulling data like that until it cannot find another entry
and thus, end?

Thanks everyone for all the help! It is greatly appreciated as always!