Unique values in one list from another list

Hi,

I'm working on a file to handle information about suppliers and components.

In the first sheet I have a list of components and the verified suppliers allowed. The same components are listed several times because they are delivered by different suppliers and the combination supplier-component is only valid until a given date.

In the second sheet I want to have a list of my suppliers. But here I want to have an easier overview and only list each supplier once.
I have tried all evening to make a unique list of suppliers based on the information in the Component-sheet, but I don't manage to do it.

Please take a look at the enclosed example file that will give you a better overview of my problem.

Does anyone have an idea how to make it? Or maybe propose a better solution.

Have you tried the Advanced filter.

To get the list on a different sheet you need to start on that sheet.

In you example...
Select A2 on the Suppliers sheet.
On the Data tab select Advanced filter.
Clear range in the List Range box and place cursor there, then select Components sheet and highlight the column of suppliers.

Click the Copy to another location radio button, Click Unique records only tick box.
Click in the Copy to box, this will take you back to the Suppliers sheet then select A2 then OK.

You should now have a unique list of suppliers.

Windy

Hi Windy,

Yes I have tried this.
But the problem is that if I add more or remove some suppliers from the component-sheet the list size doesn't automatically update in the supplier-sheet.

So then I am unable to filter or sort suppliers

Well the more I'm thinking about this I see lots of disadvantages as I need information in the supplier-sheet as well.

If the supplier list automatically gets longer or shorter depending on information about components I might loose information if I clear wrong cell value by mistake.


So, now I have been making a fixed supplier list instead, and the idea is to choose a supplier on the component sheet using data validation.

Enclosed is the new file in case someone gets ideas from it.


Solved for now