identifying same or related products

[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. 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. 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

 

[Frank Kabel]

Hi
try the following approach
Assumptions:
- column F stores your 'universal' numbers
- row 1 is a heading row
- column A stores the product names

1. Format column F with the custom format "P"00
2. In F2 (the first data row) enter the number 1 (this should be
displayed as 'P01')
3. In F3 enter the following formula
=IF(ISERROR(MATCH(A3,$A$2:$A2,0)),MAX($F$2:$F2)+1,VLOOKUP(A3,$A$2:$F2,6
,0))
copy this formula down

 

[Terry]

Unfortunately Franks solution did not return the correct
results, so either I am applying his answer incorrectly or
maybe I didn't explain things right. My description
column probably doesn't even need to be in the formula
because I only need to relate all the other columns to
each other. If any Product #'s in the 1st row are
anywhere else in the spreadsheet(no matter what column)
column F should be PO1, then the next row that does not
have a "P" numberyet, will have P02 for Column F and any
Product #'s that are in this 1st P02 row will be searched
for throughout the spreadsheet and be assigned P02 also.
And so on.

Again,
Thanks

 

[Frank Kabel]

Hi Terry
what kind of wrong results did you get. I tested the solution and
(though it functions a little bit different than your description) it
produced the correct results. Sou you may post (as plain text please,
no attchments) some sample rows with your data, my function results and
your expected results

 

[Terry]

Description Product #1 Product #2 Product #3
Product #4 Universal Number
Windex 002003 00203mon 45792-246 P01
Xtreme Glass 15429 45792-246 88424-001
2
Bic Pen 11343 7132150 3
Yellow Notepad - mead brand 11908
4
White Notepad 77889 67890 5
Squeeky Clean Glass 57769 45792-246
88424-001 6
Yellow Notepad - officemax brand 79602 11908
7
White Notepad Mead 77456 24789 8
#2 pencil 114-78221 9


This is what I needed Col F to show:

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

 

[Bernie Deitrick]

Terry,

Please don't multi-post the same question in different groups. It wastes a
lot of effort.

See my reply in the worksheet.functions group.

HTH,
Bernie
MS Excel MVP

 

[Terry]

Sorry about the multi-posting. I am new at this and
couldn't find my original posting. Your reply to my other
listing gave me more of what I need, but it still did not
work right for me. The formula did not catch that product
11908 was in 2 different rows, so it didn't give me
the "P" number of "P03" in both instances. I really do
appreciate any more help that you can give me.
Terry

 

[Bernie Deitrick]

Terry,

Is the 11908 in the same or different columns?

Bernie
MS Excel MVP

 

[Terry]

This is the result I came up using the formula you gave me:

Description Product #1 Product #2 Product #3
Product #4 Universal Number
Windex 2003 00203mon 45792-246 P01
Xtreme Glass 15429 45792-246 88424-001
2
Bic Pen 11343 7132150 3
Yellow Notepad - mead brand 11908
4
White Notepad 77889 67890 5
Squeeky Clean Glass 57769 45792-246
88424-001 6
Yellow Notepad - officemax brand 79602 11908
7
White Notepad Mead 77456 24789 8
#2 pencil 114-78221 9
#2 pencil 114-78221 9

This is what I needed Col F to show:

Description Product #1 Product #2 Product #3
Product #4 Universal Number
Windex 2003 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

 

[Terry]

The 11908 is in a different column. It is 3 rows down and
1 to the right of the 1st 11908. In other words, the 1st
11908 is in cell B5, and the second one is in cell C8.
Maybe it can't be done because it would cause a circular
reference. I need to always look at Every product #, I
can't just look at previous rows, or previous columns.

Again,
Thanks