[DipyDawg]

I have approximately 143 stores in my spreadsheet...and approximately 153
different products that could be placed in any of the stores. Some stores
carry the exact same products...others don't. Other than a pivot table
extracting the information...is there a formula I can use to generate a list
of all stores that match each other's product listing. The products are
title by product number if that matters in listing.......In other words...I
need a formula that would list what stores have the exact same
assortment/product...HELP!!!! :)

[joel]

The best way is with a sort. A macro can help

On Header Row list all the products on Row 1 From Columns B to Column 154
(EX) which is 153 columns. List the stores in column A from Rows 2 to 144.
Then place a 1 in each cell where a store has a product.

Now sort the table three column at a time from Column B to column 154. The
stores with the same product will be on adjacent rows. To help figure out
which rows match you can use conditional formating to highlight in Red when a
cell doesn't match the row above. Any rows (stores) with Red cells won't
match the row above.

You can add in Column 155 a count of the number of cells that match the row
above
In cell EY3 New column
=SUMPRODUCT(--(B3:EX3=B2:EX2))
The formula will equal 154 for a store that matches the row above.

I know this is a lot of work. A Macro can build the table, sort the table
and add the formulas. It also can be done manually.


"DipyDawg" wrote:

I have approximately 143 stores in my spreadsheet...and approximately 153
different products that could be placed in any of the stores. Some stores
carry the exact same products...others don't. Other than a pivot table
extracting the information...is there a formula I can use to generate a list
of all stores that match each other's product listing. The products are
title by product number if that matters in listing.......In other words...I
need a formula that would list what stores have the exact same
assortment/product...HELP!!!! :)