# Alternatives matrix

#### Danieljfern

##### New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Replies
14
Views
393
Replies
5
Views
161
Replies
4
Views
171
Replies
1
Views
74
Replies
4
Views
189

1,196,369
Messages
6,014,889
Members
441,854
Latest member
Amstaff

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

### Which adblocker are you using?

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

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