I need to create a looping Vlookup function in VBA

[Sweedler]

Hello

I have a list of names that need to have several formulas carried out on them, but that formula needs to also be based on the amount of names. The names are in column E of my document, and a list of numbers (from 1 to100) in column A. What I want to do is group the names into random groups of either 3-4 or 5. And then I would ideally need to be able to do the same thing again, but trying to make sure that the groups are not the same as they were last time.

What I did before trying to do this was VBA was the following:
For each name: in column C: =IF(E2="","",RANDBETWEEN(100,900))
For each name: in column D: =IFERROR(9+RANK.EQ(B2,$B$2:$B$101)+COUNTIF($B$2:B2,B2)-1,"")
For each name: in column E: =IFERROR(RANK(C2,$C$2:$C$101),"")

The next step was to use =IFERROR(VLOOKUP(A2,$D$2:$E$101,2,FALSE),"") in order to slot the names, at random, into prepared groups of 3-4 or 5.

I would like to have this be upgraded to a VBA formula

 

[Snakehips]

@Sweedler Is the below of any value?

Currently creates a randomised list in G based on names in E. Using C as a temporary helper column.

Sub RandNames()
Dim NamesRng As Range
Dim RandRng As Range

Set NamesRng = Range("G2:G101")   'Range for randomised  list of names?
Set RandRng = Range("C2:C101") 'Range for temporay ' helper'  rand numbers

Application.ScreenUpdating = False

   
RandRng.Formula = "=IF($E2="""","""",RAND())"
NamesRng.Formula = ("=IFERROR(INDEX($E$2:$E$101,RANK(C2,C$2:C$101)),"""")")
NamesRng.Value = NamesRng.Value
RandRng.ClearContents

Application.ScreenUpdating = True
End Sub

 

[Sweedler]

Hello

Well, it is a good start. But I need the names to be grouped into groups of 3-4- or 5.

Ideally those names would be entered into columns where I could have a header denoting the Table number.

I would also need the range looked into to be based on the number of names entered into column E.

 

[Snakehips]

Perhaps you could help by illustrating a scenario or two?