Create an array of names

Hi.  If someone has a solution to my problem in either Access or Excel, it would be much appreciated.

Currently, I have a file containing three columns.  The first column is  department number. The second column is an employee's first name and the third column is an employee's last name.  It looks like this:

DeptNo        FirstName       LastName
1A               John                Smith
1A               Mary                Johnson
2D               Tom                  Brady
2D               Barry                White
2D               Jan                   Brady
2D               Luke                 SkyWalker
7F                Rupert               Pupkin

There can be up to 4 first and last names associated with a particular department number.
What I'm looking to do is create a file that has only 1 record for each department number with the associated first and last names being placed in an "array" (if that's the proper way to describe it) into columns to the right of the department number. The expected results for the above file would be:

DeptNo   FirstName1 LastName1 FirstName2  LastName2 FirstName3 LastName3 FirstName4  LastName4
1A          John            Smith          Mary            Johnson
2D          Tom             Brady         Barry           White          Jan              Brady         Luke             Skywalker
7F          Rupert         Pupkin

I want to wind up with only 1 record per department number.Start Free Trial