Formula to differentiate between product lines

B

bchappell

OK, here is my problem. I have to build a pricing spreadsheet to price
products that cross product lines. I need to be able to input a nomenclature
from either line and get the pricing for either line. eg:

Product 1 Product 2
W3030 W3030
WMGD3030 W3030
WDFMGD3030 W3030
WMGD3030MI W3030
B30ST B30HDS
B30DDT B30HDS
BDF30FDS BDF30FDS

Some items are the same in both product lines, but product line 1 has more
options than product line 2. When an item is chosen in product line 1 that is
not available in product line 2 I need it to downgrade to the appropriate
product.

Can anyone help?
 
R

RagDyeR

Where does the pricing come in?

Are the first 4 items under Product 2 the same price as their counterparts
under Product 1?

If so, wouldn't a simple Vlookup formula work?

If not, how would you differentiate between them?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

OK, here is my problem. I have to build a pricing spreadsheet to price
products that cross product lines. I need to be able to input a nomenclature
from either line and get the pricing for either line. eg:

Product 1 Product 2
W3030 W3030
WMGD3030 W3030
WDFMGD3030 W3030
WMGD3030MI W3030
B30ST B30HDS
B30DDT B30HDS
BDF30FDS BDF30FDS

Some items are the same in both product lines, but product line 1 has more
options than product line 2. When an item is chosen in product line 1 that
is
not available in product line 2 I need it to downgrade to the appropriate
product.

Can anyone help?
 
B

bchappell

Sorry, didn't give enough info...

OK, here is what I am doing.
User will input nomenclature into A1(PL1). I then need B1 to look at A1 and
pull the nomenclature for PL2 and put it into B1. After this is done I will
write out the Index-Match functions that will run off of A1 and B1
appropriately.

And to answer your other question. The first 4 item examples in PL1 are not
the same price. The first 4 item examples in PL2 are the closest equivalent
product to the item in PL1 and are the same price.

Thanks for any help you can provide.
 
R

RagDyer

I never admitted to be overly bright, but I don't understand what you're
trying to do.

According to your explanation,

User enters a name that is resident in PL1 -
A formula will return the corresponding name from PL2 -
And then you will create another formula to match up the PL1 and PL2 names -

Didn't the first formula accomplish that last action at the outset?

What am I missing ... OR ... what are you not explaining?
 
B

bchappell

Sorry about that, I have been working on this so long I forget that it isn't
readily obvious to anyone else.

What I am doing is setting up a worksheet that will accept input into A1. B1
will look at A1 and determine what item corresponds to A1 and put it into B1.
Then C1-.... will have Index-Match functions that will look at the
appropriate cell, A1 or B1, and then the correct DB and pull up the price of
the item.

EG.
A1= W3030
B1 then looks at A1 and determines that W3030 is the same in both lines.
B1= W3030
C1 then looks at A1 and Index-Matches to find the price in appropriate DB.
C1=333

or
A1= WMGD3030
B1 then looks at A1 and since the MGD option is not available in PL2, strips
out the MGD and puts W3030 in B1.
B1= W3030
C1 Index-Match functions then lookup A1 and puts the price in C1.
C1= 400
D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1.
D1= 333.

I hope this makes it a little clearer and thanks for any help you can
provide.
 
R

RagDyeR

Do you have a complete 2 column datalist of Product1 and it's corresponding
Product2 counterpart?

How do you determine the proper database to use for pricing, and where are
these Db's located, and how many of them are there?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Sorry about that, I have been working on this so long I forget that it isn't
readily obvious to anyone else.

What I am doing is setting up a worksheet that will accept input into A1. B1
will look at A1 and determine what item corresponds to A1 and put it into
B1.
Then C1-.... will have Index-Match functions that will look at the
appropriate cell, A1 or B1, and then the correct DB and pull up the price of
the item.

EG.
A1= W3030
B1 then looks at A1 and determines that W3030 is the same in both lines.
B1= W3030
C1 then looks at A1 and Index-Matches to find the price in appropriate DB.
C1=333

or
A1= WMGD3030
B1 then looks at A1 and since the MGD option is not available in PL2, strips
out the MGD and puts W3030 in B1.
B1= W3030
C1 Index-Match functions then lookup A1 and puts the price in C1.
C1= 400
D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1.
D1= 333.

I hope this makes it a little clearer and thanks for any help you can
provide.
 
B

bchappell

Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3
product lines I am working with, and I know of 2 more that will be added in
the future. The DB's will be resident in the workbook, located on different
worksheets for each one. Determination of which DB to access is written into
the Index-Match functions I am going to use.

Once again Thanks for your help.
 
R

RagDyer

If you already have the formulas constructed to pull the pricing from the
various data bases, what exactly do you need help with?

To get the matching product name into B1 when a user enters a name in A1?
That could be a simple Vlookup formula.

Say with the PL1 and PL2 lists on Sheet2, in A1 to B50,
And the user enters a name in A1 of Sheet1,
This formula could be in B1 to return the matching name:

=Vlookup(A1,Sheet2!A1:B50,2,0)

Is this what you're looking for?
 
B

bchappell

To get the matching product name into B1 when a user enters a name in A1?
That could be a simple Vlookup formula.
Click to expand...

True, it is a simple VLookup for the products that correspond. The problem
comes in where there is no corresponding product. EG: WMGD3030 is a product
in PL1, but it doesn't exist in PL2.
When someones enters WMGD3030 I would like the cell to look at PL2 determine
if the product is available or not, then if it isn't, find the nearest
equivalent and put it into the cell. EG: WMGD3030 becomes W3030.

I guess the problem I am having is determining the correct order of events
that need to take place for it to do that.

Look at A1
Determine if product is available, then either A: use available or B: find
equivalent
 
R

RagDyeR

In your example in your OP, you displayed alternate options in PL2 on the
same row as the full named product in PL1.

Isn't that how your datalist is constructed?


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

bchappell said:
To get the matching product name into B1 when a user enters a name in A1?
That could be a simple Vlookup formula.
Click to expand...

True, it is a simple VLookup for the products that correspond. The problem
comes in where there is no corresponding product. EG: WMGD3030 is a product
in PL1, but it doesn't exist in PL2.
When someones enters WMGD3030 I would like the cell to look at PL2 determine
if the product is available or not, then if it isn't, find the nearest
equivalent and put it into the cell. EG: WMGD3030 becomes W3030.

I guess the problem I am having is determining the correct order of events
that need to take place for it to do that.

Look at A1
Determine if product is available, then either A: use available or B: find
equivalent
 
B

bchappell

No, that is part of the problem. I have to build this on the premise that
each year when we receive new DB's, we can just plug them into the
appropriate page and everything will continue to work as normal. These DB's
are provided by the manufacturers of the different products.

Because of this I cannot reformat the DB's. If it wasn't for this issue I
wouldn't have a problem. I could just reformat the DB's as required.

The problem is no one here (myself included) is really adept at Excel. I am
probably the most adept of anyone here. Which is why that brought this to me.
 
R

RagDyer

SO ... your example of PL1 and PL2 is *not* accurate!

Let's start from the beginning.

Can you post exact examples of the 2 lists?

Are they paired up in adjoining columns or not?
OR, are they 2 completely separate entities, with no *physical* connection
with each other.
For example, could PL1 be in A20 to A50,
And PL2 in A60 to A75?

What is the determining factor that creates (accepts) a match of
*unidentical* names.
 
B

bchappell

RagDyer thanks for your assistance. I decided to do this a different way. I
created a conversion matrix for the different product lines and used this to
tie everything together. With over 5,000 different sku's it wasn't easy but
it is working now. Of course when the new product lines are added I may have
to think of something else to make everything work together.

Again thanks for all your time.

Barron
 
R

RagDyeR

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

RagDyer thanks for your assistance. I decided to do this a different way. I
created a conversion matrix for the different product lines and used this to
tie everything together. With over 5,000 different sku's it wasn't easy but
it is working now. Of course when the new product lines are added I may have
to think of something else to make everything work together.

Again thanks for all your time.

Barron
 
Top