How to format a vendor list when vendors offer several different products?

[FINBRA]

I am trying to build a source book for my organization as we are sales and distribution and sourcing is a daily responsibility. Our vendors can offer anywhere from 1-20 different kinds of products in how we would categorize them, and since we are a national company those vendors have several different contacts based on what region you are located in. Below is the information I am needing to incorporate, and I am trying to avoid having the vendor listed once and merging cells vertically to include all the different contacts and products they offer. This source book also can get updated frequently so I am needing something that can be updated without being too tall of an ask. End goal here is for a salesperson to be able to go in to the source book and select the product they are needing to source, and it return the range of vendors and contacts available with the other information associated with each.

Columns for data:
Vendor, Contact(s), Email(s), Phone(s), Region(s), Shipping Location(s), Product(s), Vendor Rank, Freight Terms, Notes

I had originally thought of building a master list with all the contact information duplicated by the different products, but if a contact needed to get updated, you would need to update all entries which could be cumbersome. It would also look really messy until sorted by product. Trying to see if I can build vendor contacts with all this information, and then query/pivot table/etc to return the vendors that match the criteria. This is above my excel expertise, so let me know if this makes sense or is too complicated!

 

[shift-del]

Your fields in plurals will most likely have to be seperate tables. That is the concecpt of database normalization.

Database normalization - Wikipedia

en.wikipedia.org

 

[Micron]

Probably not the answer you'd like to get, but this is a job for a relational database.

Using Access or Excel to manage your data - Microsoft Support

Learn when to use Access and when to use Excel to manage your data.

support.microsoft.com

Database vs Spreadsheet: Full Comparison – 365 Data Science

What sets these two data science terms apart: database vs spreadsheet? We compare their pros and cons, similarities and differences. Read now!

365datascience.com

 

[FINBRA]

Probably not the answer you'd like to get, but this is a job for a relational database.

Using Access or Excel to manage your data - Microsoft Support

Learn when to use Access and when to use Excel to manage your data.

support.microsoft.com

Database vs Spreadsheet: Full Comparison – 365 Data Science

What sets these two data science terms apart: database vs spreadsheet? We compare their pros and cons, similarities and differences. Read now!

365datascience.com

You are right, I was hoping to be able to solve this in excel. I’m not sure more than 1% of people in my organization even know what access is and I need to see if it is offered in our 365 account. From the read you provided, it seems like the best solution though for sure.

 

[Micron]

If you stick with Excel, make sure you understand its limitations wrt things like concurrent users, data volume, data integrity, etc. Don't know if you have to convince anyone but if that is the case and if this were me I'd do some more research to bolster this position on the matter. BTW, only the developer(s) need Access. Everyone else can use the free runtime version. If they can work with an Excel userform, they can work with an Access form (they're not called userforms). Also, Access reports are way better than anything you can make in Excel (at least that's my opinion).

 

[FINBRA]

If you stick with Excel, make sure you understand its limitations wrt things like concurrent users, data volume, data integrity, etc. Don't know if you have to convince anyone but if that is the case and if this were me I'd do some more research to bolster this position on the matter. BTW, only the developer(s) need Access. Everyone else can use the free runtime version. If they can work with an Excel userform, they can work with an Access form (they're not called userforms). Also, Access reports are way better than anything you can make in Excel (at least that's my opinion).

I am diving into the Access idea now that I’ve identified we all have it installed on our computers already. The only thing I am struggling with as I think of how to build the database is how to get multiple product types linked to one vendor ID. Below is my current plan to make this work.

Create a table for vendors and products offered.
Create a table for contacts linked to that vendor.
Create a query where you can set the product you are looking for to return the vendor and all contacts associated with it.
Create a form to add new vendors/contacts.

I just don’t see how to have all the different products linked to one vendor in a single row. Would it be better to have the vendor duplicated in the first table for as many products as they offer as long as the vendor ID was the same as well?

 

[Micron]

Create a table for vendors and products offered.

No. That is at least 2 tables.

I just don’t see how to have all the different products linked to one vendor in a single row.

Because you do not do this. You are using your Excel brain. Sheets are wide and short - db tables are narrow and tall, to put it one way. You need to forget all of that Excel stuff and learn about database normalization if you do this. This won't be a walk in the park, but there are plenty of forums for Access and also Google. I have often posted a lot of links that I've chosen to provide what I think is an excellent start. I'd urge you to review them all before you do anything and make Coles notes before you create anything. Then understand normalization. When you think you've got it, create a new thread to have your ideas vetted. In that thread, you might be entreated to create your tables in Access, create the relationships and post a pic of the relationships window. If anyone sees special characters or spaces in your object names (e.g. tables or fields) it will be obvious that you either didn't follow the link about naming things, or you chose to ignore it.

FWIW, although I help out here, Access is my main thing, not Excel.

 

[FINBRA]

So as I was poking around until I saw your reply, I found how to add a field that will allow several choices from a dropdown. Will this not allow me to combine the tables and avoid redundant entries on a separate table and to query which vendor IDs has a product type assigned to them under that field?

No. That is at least 2 tables.

Because you do not do this. You are using your Excel brain. Sheets are wide and short - db tables are narrow and tall, to put it one way. You need to forget all of that Excel stuff and learn about database normalization if you do this. This won't be a walk in the park, but there are plenty of forums for Access and also Google. I have often posted a lot of links that I've chosen to provide what I think is an excellent start. I'd urge you to review them all before you do anything and make Coles notes before you create anything. Then understand normalization. When you think you've got it, create a new thread to have your ideas vetted. In that thread, you might be entreated to create your tables in Access, create the relationships and post a pic of the relationships window. If anyone sees special characters or spaces in your object names (e.g. tables or fields) it will be obvious that you either didn't follow the link about naming things, or you chose to ignore it.

FWIW, although I help out here, Access is my main thing, not Excel

 

[Micron]

I found how to add a field that will allow several choices from a dropdown.

I suspect you're referring to a table lookup field. One of the links I gave you advises not to use them, never mind that you should not be working in tables instead of forms. Sure, you will see M$ examples doing/using some of the things that the links say not to do. IMO, that is to get you into the driver's seat and give you your license after one or two lessons, to use an analogy, so that you buy into the product. Down the road, you will often pay for doing these things.

I don't know what your meaning is for "redundant" entries. If you properly normalize a db, there is no such thing. Best that you follow as many of those links as you can, or find sources on those topics that you might like better. I guarantee that if you don't normalize you will always have difficulty getting data in or out. You'll spend a lot of time on forums looking for work-arounds. You might as well stick to Excel and cobble something together if you can.