Alternatives matrix

Danieljfern

New Member
Joined
Apr 10, 2013
Messages
9
Hi

This is my first post, so I'm not sure how to upload an image of my example table, apologies if it makes no sense. I've been unable to find an answer to this (mainly because I'm not sure if I'm searching on the right term)

I have a list of 20,000 products, some of which can be used as alternatives for others, I'm trying to update the system with a comprehensive list so that we can quickly see that another product can be used where we have no stock of the other.

e.g.

Base Product 1 (lets call it A1) has 5 alternatives on our system A2, A3, A4, A5 and A6.


Base Product 2 (A2) currently has 3 alternatives on our system B1, B2 and B3.


Logic says that if A2 is an alternative for A1 then B1, B2 and B3 should be alternatives for A1 also. And so on

So with that in mind A1 actually has 8 alternatives and so do all of the 8 alternatives. (A1 to B3 are all alternatives to each other)

I need to somehow write a macro or formula to work out where the system (an excel report listing 20,000 base products in column A and their alternatives in the columns to the right) is missing alternatives, and populate my list / matrix.

Maybe an access solution would be more efficient? although i have little / no access experience.

Any help would be appreciated

Dan
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top