Organising people into groups where the same person cannot appear with another twice.

So, Ive been working on a project for a friend for a while now, but am having some trouble on a few parts.

In short, Im trying to make a template that will take a list of names (that have unique ID numbers) and randomly and equally put them into 4 sets of 8 groups, so basically 32 individual groups. There are 4 "Units", and for each unit this procedure is repeated with the same names (i.e. each unit has again 32 groups made up of the same people), but there are some restrictions:

Firstly, no one person can be grouped again with someone they have previously been in a group with in the following units.
Secondly, a person who appears in either the first or last group in a set (i.e. 1 or 8) cant appear in a 1 or 8 group again.

So far, Ive managed to use VBA to create a list of random numbers, then used some cell formulas to create an index against the names and their random ranking which gives me a list of randomised names and another formula that counts the amount of names and separates them into as even a grouping as possible.

Ive been looking at using formulas again to see if I can do something similar for the following units where those rules come into play, but have not had much luck, so am looking at VBA now, but my VBA is not really up to par.

I was thinking of maybe transposing the first list so its headed by the group number and then creating the next unit of groups by finding someone and looking at where they are in that list and looping through to find if who is grouped with who if that makes sense, but I a) dont know if thats the best way to do what im trying to do, and b) need some guidance on how that would be structured in VBA since im pretty new to it.

Any help would be massively appreciated. Ive attached what ive got so far.