Question sorting text to columns & fetching data from arrays.

I have an exported list of users from Active Directory and I noticed it tends to combine all the user groups on one cell, a little annoying but it was easy to split them into separate columns using the Text to Columns feature. However, the way the data is listed is not how I need it.

For example, it's listed like this...

A1 B1 C1 D1 E1
Grp, Usr1, Usr2, Usr3, Usr4
Grp2, Usr5, Usr1, Usr6, Usr3

But I'd like to automate the cell contents to display like this...

A1 B1
Grp, Usr1
Grp, Usr2
Grp, Usr3
Grp, Usr4
Grp2 Usr5
Grp2 Usr6
Grp2 Usr7
Grp2 Usr8
etc...

In my sheet I've been using several array formulas to lookup other values and auto create new user accounts based on the users Display Name, it's primarily a custom migration template I've built to use at work.


While I'm asking questions there is one other issue I'd like to see if I can address. One of my formulas references a long list of new auto populated users (for the new domain) and correspond to the appropriate groups/permissions. The formula I'll post in a sec lists the users from this column without creating duplicate entries...so it's just a list of the new user accounts basically...it's just an array formula.

=IF(ROWS(N$3:$N3)<="*",INDEX(AutoUsrList!$X$4:$X$1688,SMALL(IF(AutoUsrList!$X$4:$X$1688<>"",ROW(AutoUsrList!$X$4:$X$1688)-ROW(AutoUsrList!$X$4)+1),ROWS(N$3:$N3))),"")


The above formula works just fine, however it's the second part I'm trying to accomplish, I want to create another array list that checks the list like the above formula but looks at another corresponding column to see the group that matches the user account. I'll try and post what I mean...

New Domain/User 1 Group1
Group1
New Domain/User 3 Group1
New Domain/User 1 Group2
Group2
New Domain/User 6 Group2
New Domain/User 7 Group3
New Domain/User 2 Group3
Group4
New Domain/User 1 Group4
New Domain/User 3 Group4
New Domain/User 6 Group4


I want the list to check the above columns to come back with a list of the groups that correspond with each user that is not blank. I know I can fix the column manually to do this but I want an automated method. I'm about 90% complete with this template and this little part is making a snag for me. Thanks in advance!