How to auto populate other cells when selecting values in Excel drop down list?

[lougs7]

Hello All,

I have a Data Sheet with Accountant Details for all clients in columns A to F.

The Column headings are Accountant Firm Nane, Accountant, Company Name, Exposure, Account amanger etc.

The goal is to have a drop down menu from the Accountant Firm Name list, where once a firm is selected I would have a button that once clicks retrieves all the relevant data for that Accountant name (data in B:F)
The problem is when I make the drop down list, the list displays multiple entries of the same name because there are repeats in the list (column A) this is a sample Sheet in reality I have 1000 rows so a lot more repeats, I need a way around this to show unique entries in drop down only, otherwise it is too redundant. Plus I need as much help as I can get writing the code

For Ex:
User Selects Drop Down Menu of an Accounting Firm "ABC Accounting"

There is a button on the side that reads Drop Down Value and all of a sudden when clicked data populates the sheet like this:

Accountant Client Name ID # Exposure Account Manager

Paul Bryant Openlane 54251 $120,215 John Doe
Paul Bryant Yearin 69825 $882,114 Paul Awal
Paul Bryant Goodsilron 24546 $512,545 Sabrina Laury
Paul Bryant Condax 32675 $58,216 John Doe
John Smith Opentech 21548 $654,542 Paul Awal
John Smith Golddex 54987 $31,324 Sabrina Laury
Luke Ryan year-job 23355 $854,515 John Doe
Sydney Samps Isdom 22222 $213,454 Sabrina Laury

This can be done on a new sheet so it's clean or on the side and I will figure it out. Please view my Attachment of the sample workbook.
your comments will help me out a lot. How do I do this?

[Mumps1]

Try the attached file. Make your selection in the drop down list in cell H3. The data will be copied to Sheet2.

[Mumps1]

Sorry. The file wasn't attached successfully. Click this link to download your file: https://app.box.com/s/04qwr7gwoyl6evc0an34absyz9f6mndl

[Mumps1]

I made a slight change in the macro. Please use this link: https://app.box.com/s/04qwr7gwoyl6evc0an34absyz9f6mndl
If you ever have more than 500 lines of data, the macro will have to be modified.

[lougs7]

Hey Mumps1,

I am getting an error code for that link "app.box.com’s server DNS address could not be found."

It's not possible to upload here directly?

[Mumps1]

Hopefully, it attached this time.

[lougs7]

amazing thank you so much :D

[Mumps1]

You are very welcome.