Dynamic drop down lists

I am trying to create a sheet where people select a vendor, with costs adjacent to each one, for a number of parts on a spreadsheet. I've attached an example.

I want to have a data validation drop down provide a list of the vendors, however on some rows there will be 6 vendors, some there will be 2, and the order matters as each column is dedicated to different value streams.

I want to use FILTER in a single cell to provide an array of just the cells that are populated with a vendor name, meaning that the drop-down will only have the vendors, no blanks, and no costs.

Currently I have to have a series of helper columns which all point to each vendor column, skipping out the costs. This is then used as a data validation source for a drop-down, with the row being dynamic to allow each row to have it's own appropriate list. This works, but results in zeroes in the unpopulated vendor cells (which there are many as we don't always have 8 quotes). I have successfully used filter to result in a spill cell which is a start, but then I can't actually refer to it within this table to be the DV source.

You are correct - in my frenzied attempt to stop the database error I kept getting trying to post this I forgot to re-add this.

The simple reason is that people in the company who will be using this aren't comfortable with macros; the big scary warning that comes up when they open an .xlsm results in more noise than anything and then it's left unopened, despite me assuring them that it's safe. Every. Single. Time.

I'd add that I keep seeing confirmation that this isn't possible in a Table (which by your mission statement - which I totally agree with - doesn't help!) as the FILTER SPILL does actually solve this with a # at the end of the single cell validation source - if it's not in a table.

I neglected to reply to this. This would work, but I'm keen to keep all the data in the table so that the integrity of the data whilst sorting and filtering isn't affected. Currently I've compromised to having all 6 vendor selections visible and when they're blank generating a "-". It's not what I wanted, but I doubt anyone would appreciate it if I could get only the populated cells in there anyway!

Thanks for your help, I'll keep this in mind for future non-tabled data.