Excel Help for Employee Office Mailbox Assignment Master And A-z List

I am taking over the company's employee master list and mailbox assignment list spreadsheets and I'm thinking that there has to be an easier way to do this. Currently I have two separate spreadsheets with the exact same information. One spreadsheet has all of the mailboxes in order and the other spreadsheet has all of the employees listed in alphabetical order, making the mailboxes all scrambled. Which is fine, I don't care that the mailboxes are scrambled. It's alphabetized so that the employee can easily find their assigned mailbox number by searching for their last name.

The issue I am having is that I have two different groups of employees, Armed & Unarmed. In the Master List, they're all one the same sheet mixed together. On the A-Z sheet, one group (Armed) is separated from the larger group (Unarmed). So column A has the mailbox number and column B has the employee name and those two columns are for the Armed group. Columns C & D, E & F, and G & H are for the larger (Unarmed) group with the first columns C, E & G showing the mailbox number and columns D, F & H showing the employees names in alphabetical order.

Previous people would update the Master List and as the employees get hired or leave the company. So there are empty spaces scattered throughout the entire Master List. When we hire a new employee, they should get placed in the first empty space available. When someone leaves the company, you simply delete the name but not the mailbox number because that's the order in which the Master List is organized.

I want to be able to update the Master List and have the mailbox number and name show automatically on the A-Z list, preferrably in Alphabetical order if possible. But if I have to sort the list manually, then I am fine with that so long as it keeps the two groups separate. I don't know how to do that since there isn't anything indicating who belongs in which group. I just finished cleaning up the A-Z list finding multiple people listed twice, once in each group for some reason. They were easy to find because they are members of management and they don't need a mailbox.

I'm new to this forum and I don't see how to attach my spreadsheet so that people can see what it is I am dealing with so I have to try and insert a snippet as that's all I can see to do for anyone willing and able to assist me.

Excel Spreadsheet:

A	B	C	D	E	F	G	H
ARMED	OFFICERS	UNARMED	OFFICERS	UNARMED	OFFICERS	UNARMED	OFFICERS
Box	Name	Box	Name	Box	Name	Box	Name
I14	Akin, W	E7	Allen, D	J3	Foster, T	I12	Pollard, D
G5	Boyd, J	H2	Anderson, D	B7	Foxworth, E	N2	Quickert, D
F5	Brown, T	J1	Bah, A	K13	Frazier, S	N15	Rafiyq, A
B12	Campbell, R	A6	Baldwin, S	F3	Fuller, R	I13	Ressler, H
J6	Casey, C	L7	Bloomfield, B	J13	Gardner, W	C2	Reyes, I

The example above is of the A-Z list that is completed manually from the Master list. If what I am wanting isn't possible, then I guess I'll just continue to do it the way that it was designed. But I believe there has to be a better way to do this so that I am not cleaning it up every time someone else who doesn't know what they're doing touches it.

Please help, even if it's just helping my already overworked, exhausted brain come up with an idea that it should already know but is too tired to think of it. I am usually pretty good with Excel and formulas. But with the office manager quitting, me discovering everything she half-assed and I'm busy cleaning it all up and my brain is scattered in many different places.

Employee Office Mailbox Assignment Master and A-Z List

Excel Help for Employee Office Mailbox Assignment Master And A-z List in Excel Business Valuation Template

Employee Office Mailbox Assignment Master And A-z List

	INDEX and MATCH Rate this: (4/5 from 2 votes) I think that your best option here is a combination of INDEX and MATCH. This gives you flexibility on horizontal and vertical matching and is more efficient than LOOKUP formulas. See: https://exceljet.net/index-and-match
	Excel Business Forums Administrator
	Posted by Excel Helper on 16 Dec 2021

	hi Rate this: (4/5 from 2 votes) thank you so much space bar clicker
	Posted by Wheeler on 25 Mar 2024