I’ve been really struggling trying to automate a spreadsheet for my job to keep better track of who is staying in which bunkhouse room and for how long.

Bunkhouse Master – The primary workbook that contains the master list in a formal table. Also has a sheet devoted to a control panel with a button to prompt the user to select an excel workbook to open (the new bunkhouse roster).

Master List Table – Table contains 5 columns: Arrival Date, Departure Date, Bunkhouse Room, Occupant Name, & Keycode. Entries must be added to the bottom of this table with the current information preserved.

New Bunkhouse Roster – The workbook exported from our scheduling system has the information organized into columns on the first sheet, but it does not come set up in a formal table. Columns include: Arrival Date, Departure Date, Bunkhouse Room, Occupant First Name, Occupant Last Name, Keyhole, Last Updated.

The goal is to automate it so:
1. User clicks button on the Control Panel in the Master Workbook, prompts user for excel file. (I cannot guarantee the name or location of the file)

2. Copies information from the first 6 columns of supplied excel sheet to the bottom of the Master List Table. Occupant’s name should be combined to appear as: “Last, First”

3. At the end, any and all duplicates are removed from the Master List.

I have tried going through on my own and have managed to Frankenstein some stuff together that will automate opening the requested sheet. I have also found an easy way to remove duplicates from the table at the end, but copying from a supplied spreadsheet to the bottom of the master table has proven beyond my experience. I’m struggling to get it to copy all the information I need without having specific files/locations for the spreadsheet im adding from or losing what’s already on the Master List. Combineing the Occupant name isn’t necessary but more of a want