Help with populating cells via VLoopUp or Other Method (open to suggestions)

Hi experts

I need to Excel expert to guide me on the best way to do something.

Part 1:
I want to populate cell B1 of the 'Main Page' worksheet with a Company Name from Company Names in the 'Contacts" worksheet. I've used a VLoopUp which returns my the company names, but there are a whole heap of duplicates, which as the list grows will be annoying. I need to filter this list down more by selecting a contact name from the company, so I'm not sure if the best approach is to have a drop-down list of the company names and the contact, or to filter the company name list down so no duplicates appear, and then under cell B5 have a selection of Contact Names that only appear from that selected Company Name.

Part 2:
What function/s should I use to achieve the solution to part 1, I.e. can you have a Vlookup that displays to columns, or can you stop showing duplicates in the Vlookup? And in Cell B5 can I filter the drop-down list to only show names connected with the selected Company Names.

Help! I've attached my file.
Populate-Address.xlsx
Fi69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
There are a couple of ways one can go about this.  One way is to use VBA to create a list "on-the-fly" as data is changed.  Another way is to create dynamic ranges that will capture the unique data and present that in Data Validation.  I'm showing the latter method here.

First, to create a list of unique company names, an additional table will need to be created.  In it, the following formula would be inserted in the second row and copied down as far as needed:
=IFERROR(INDEX($A$2:$A$33,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$33),0),0)),"")

This will produce a list of unique values - company names - from your main table.  This is then made into an Excel Table and assigned the name "Table2".

Second, a dynamic range name is created to reference only the non-blank cells in this new table.  I'll call it "CompList" and give it the following formula:
=OFFSET(Contacts!$G$1,1,0,MATCH("",Table2,0)-1,1)

Third, we'll assign this range to the data validation in cell B1 of your "Main Page" worksheet.  Just change the Data Validation source to "=CompList".  Now, only the four companies are visible in the drop down.

Since only the company name is shown in cell B1, we'll need to show the valid contact names for the selected company in cell B5.  Again, we'll create a dynamic range name called "ContactList" and give it the following formula:
=OFFSET(Contacts!$A$1,MATCH('Main Page'!$B$1,CompanyName,0),3,COUNTIF(CompanyName,'Main Page'!$B$1),1)

Lastly, we'll assign this range name to the data validation for cell B5 (setting the Source to "=ContactList").  This ensures that only contacts aligned with the company selected in B1 will show up.

I've taken the liberty of removing the "Contacts" range name, as it was not needed in this example.  I've attached a modified file for your review.

Regards,
-Glenn
EE-Populate-Address.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please find attached.
Populate-Address.xlsx
0
Fi69Author Commented:
OMG Glen, that's fantastic. I'm going to look at it more closely now and try and understand the process.
0
Fi69Author Commented:
Thank you!
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.