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

F

FINBRA

New Member
Joined
Mar 30, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Upvote 1
Solution
Micron said:
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 support.microsoft.com
365datascience.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 365datascience.com
Click to expand...
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.
 
Upvote 0
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).
 
Upvote 0
Micron said:
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).
Click to expand...
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?
 
Upvote 0
FINBRA said:
Create a table for vendors and products offered.
Click to expand...
No. That is at least 2 tables.
FINBRA said:
I just don’t see how to have all the different products linked to one vendor in a single row.
Click to expand...
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.
 
Upvote 0
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?
Micron said:
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
Click to expand...
 
Upvote 0
FINBRA said:
I found how to add a field that will allow several choices from a dropdown.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Copying and pasting onto mastersheet but files have different formats (need if statement help)
2
Replies
12
Views
1K
NateSC
NateSC
D
  • Solved
Automation for training sheet
2
Replies
10
Views
159
Snakehips
Snakehips
M
  • Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
173
bobaol
B
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
729
MissDB101
M
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
337
etaf
etaf

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

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