T
terry
I hope someone can help because this will take me 2 weeks
to figure out the way that I am doing is as I am new at
Excel, and the vlookup I used didn't seem to work for me.
my column A does not even have to figure into the formula
since I am only looking for PRODUCT # relationships. What
I am trying to do is create a list of all
related products. For example Windex, buying it from one
store, it might be listed in inventory as product # 123,
and buying it at another store, it might be listed as
product # 456 and at yet another store in may be product #
789, and product 789 might be related to product 201
somewhere else on my spreadsheet. So I want to know that
when I pay for any of these products, that they are all
the same or similar products,
(some may be off brands). So my spreadsheet has 6
columns . 1 column for the description of the product, 4
for the different vendors that I use and a 6th column
labeled "Universal Number". This Universal number is the
number that I am going to give all Product numbers that
refer to similar or same products. My first related
products I will give a Universal Number of P01, my next
Universal Number will be P02, then P03, P04, P05, etc.
So the above products ( 123,456,& 789 ) will now have a
Universal Number of P01 given to them since they are all
for the same product.
My 1st column is a product description be it name brand or
off brand.
The next 4 columns don't always have something in them,
because these columns are really product numbers that can
be substituted for what is in column 1 or old numbers that
are no longer valid, but are really just there so that we
know what product number the product used to be known
as.
So I need a formula that can look at row 1 and assign P01
to the 6th column. Then look throughout the rest of the
spreadsheet and name any rows 6th column throughout the
rest of the spreadsheet P01 if ANY of the product numbers
in columns 2-5 have any of the same product numbers as the
row that was just assigned P01. And then the formula
needs to go to the next row, and if that row was not
assigned P01, it needs to assign the column 6 "P02" and
then check the rest of the spreadsheet to see if any of
the products in the row assigned "P02" exist, and if they
do, assign the column 6 of those rows P02 also.
So here is an example of a spreadsheet :
Description Product #1 Product #2 Product #3
Product #4 Universal Number
Windex 002003 00203mon 45792-246 P01
Xtreme Glass 15429 45792-246 88424-001
P01
Bic Pen 11343 7132150 P02
Yellow Notepad - mead brand 11908
P03
White Notepad 77889 67890 P04
Squeeky Clean Glass 57769 66793-221
88424-001 P01
Yellow Notepad - officemax brand 79602 11908
P03
White Notepad Mead 77456 24789ZNC
67890 P04
#2 pencil 114-78221 P05
As you can see, the copy paste that I did, didn't paste
the excel spreadsheet right, but I think if you paste it
into Excel, it will paste ok.
Thanks
to figure out the way that I am doing is as I am new at
Excel, and the vlookup I used didn't seem to work for me.
my column A does not even have to figure into the formula
since I am only looking for PRODUCT # relationships. What
I am trying to do is create a list of all
related products. For example Windex, buying it from one
store, it might be listed in inventory as product # 123,
and buying it at another store, it might be listed as
product # 456 and at yet another store in may be product #
789, and product 789 might be related to product 201
somewhere else on my spreadsheet. So I want to know that
when I pay for any of these products, that they are all
the same or similar products,
(some may be off brands). So my spreadsheet has 6
columns . 1 column for the description of the product, 4
for the different vendors that I use and a 6th column
labeled "Universal Number". This Universal number is the
number that I am going to give all Product numbers that
refer to similar or same products. My first related
products I will give a Universal Number of P01, my next
Universal Number will be P02, then P03, P04, P05, etc.
So the above products ( 123,456,& 789 ) will now have a
Universal Number of P01 given to them since they are all
for the same product.
My 1st column is a product description be it name brand or
off brand.
The next 4 columns don't always have something in them,
because these columns are really product numbers that can
be substituted for what is in column 1 or old numbers that
are no longer valid, but are really just there so that we
know what product number the product used to be known
as.
So I need a formula that can look at row 1 and assign P01
to the 6th column. Then look throughout the rest of the
spreadsheet and name any rows 6th column throughout the
rest of the spreadsheet P01 if ANY of the product numbers
in columns 2-5 have any of the same product numbers as the
row that was just assigned P01. And then the formula
needs to go to the next row, and if that row was not
assigned P01, it needs to assign the column 6 "P02" and
then check the rest of the spreadsheet to see if any of
the products in the row assigned "P02" exist, and if they
do, assign the column 6 of those rows P02 also.
So here is an example of a spreadsheet :
Description Product #1 Product #2 Product #3
Product #4 Universal Number
Windex 002003 00203mon 45792-246 P01
Xtreme Glass 15429 45792-246 88424-001
P01
Bic Pen 11343 7132150 P02
Yellow Notepad - mead brand 11908
P03
White Notepad 77889 67890 P04
Squeeky Clean Glass 57769 66793-221
88424-001 P01
Yellow Notepad - officemax brand 79602 11908
P03
White Notepad Mead 77456 24789ZNC
67890 P04
#2 pencil 114-78221 P05
As you can see, the copy paste that I did, didn't paste
the excel spreadsheet right, but I think if you paste it
into Excel, it will paste ok.
Thanks