Randomizing employee list with pairings without future duplicates

Hi there!

New to this forum and am excited to be a member. I have been on the search for an answer and am having a really hard time figuring it out. I am trying to work on a list that I will be randomizing each month to pair employees with someone new. I am having issues with the pairing part not showing up duplicates and also making sure the two people are paired together. (ie Alli is paired with Bobbie, but for some reason down the excel sheet Bobbie is paired with Steven). I felt like I needed to start all over as a new start and that wasn't working either. If I could get advice on how or what to do. I have seen some people do it with macros, but I don't even know where to begin with that. I have attached a mockup excel sheet with different information so I don't give employee information. However, the excel functions I have in it are still there.

Example excel sheet.xlsx

Hi,

I'm zero with VBA

in A2 numbers: from 1 to 106

In C2

=IFERROR(AGGREGATE(15,6,ROW($1:$106)/(COUNTIF(C$1:C1,ROW($1:$106))=0),RANDBETWEEN(1,106-COUNT(C$1:C1))),"")


to get a random list without duplicates of numbers from 1 to 106

In D2 to be copied below

=VLOOKUP(C2,$A$2:$B$107,2;0)

or

=VLOOKUP(C2,$A$2:$B$107,2)

A binary search is Ok for a complete list of numbers

in D1

=SUMPRODUCT(--(A2:A107<>C2:C107))

Press F9 untill you read 106 in D1

Then copy names in column D and paste special values in column E (or F or G)

Hope it could be of some help.

(in my case I would not run to be "paired" with at least 75% (maybe more) of my colleagues: formulas in column C would be more difficult )

Your sample sheet does not make sense. Your sheet shows no-one paired with anyone!!

Please repost showing ONLY what you are starting with and a few (manually prepared) rows showing your desired result.

Add your revised sheet in a new post in THIS thread. DO NOT retrospectively edit post 1.