studentlearner
New Member
- Joined
- Oct 7, 2021
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
So I have a list of data (E2:E13) that is to be randomized, after randomizing, I am to assign it to employees 2 and 3, the problem is that I'll have to check the front rows so that to not assign the same values and therefore preventing duplicates of the rows A and B.
Heres a data I'm working with:
And as you can see there's a duplicate on A7:C7 and A10:C10 even after the function is completed and I wish to prevent that, any help would be great thanks!
here's the macro i'm using to assign cells:
Sub Fill_Blanks_From_List2()
Dim StartRow As Long
Dim rA As Range
StartRow = 2
For Each rA In Range("B2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
rA.Value = Range("G" & StartRow).Resize(rA.Count).Value
rA.Font.Color = vbRed
StartRow = StartRow + rA.Count
Next rA
End Sub
Sub Fill_Blanks_From_List3()
Dim StartRow As Long
Dim rA As Range
StartRow = 2
For Each rA In Range("C2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
rA.Value = Range("G" & StartRow).Resize(rA.Count).Value
rA.Font.Color = vbRed
StartRow = StartRow + rA.Count
Next rA
End Sub
Heres a data I'm working with:
AEL - Master Marking.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Employee 1 | Employee 2 | Employee 3 | Data | Randomizer | Radomizer No Formula | |||
2 | Loki | Wiki | Thor | Senior | Senior | ||||
3 | Thor | Tony | Kuku | Luffy | |||||
4 | Tony | Loki | Puk | Sanku | |||||
5 | Kim | Peanut | Luffy | Thor | |||||
6 | Peanut | Cookie | Kim | Sanku | Momo | ||||
7 | Puk | Kisama | Momo | Peanut | |||||
8 | Luffy | Sanku | Kisama | Kim | |||||
9 | Senior | Momo | Kim | Kuku | |||||
10 | Kuku | Kuku | Peanut | Kisama | |||||
11 | Momo | Senior | Tony | Puk | |||||
12 | Sanku | Keke | Luffy | Thor | Tony | ||||
13 | Kisama | Puk | Loki | Loki | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F13 | F2 | =SORTBY(E2:E13,RANDARRAY(ROWS(E2:E13))) |
Dynamic array formulas. |
And as you can see there's a duplicate on A7:C7 and A10:C10 even after the function is completed and I wish to prevent that, any help would be great thanks!
AEL - Master Marking.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Employee 1 | Employee 2 | Employee 3 | Data | Randomizer | Radomizer No Formula | |||
2 | Loki | Wiki | Kuku | Thor | Momo | Kuku | |||
3 | Thor | Senior | Sanku | Tony | Kim | Sanku | |||
4 | Tony | Luffy | Peanut | Loki | Senior | Peanut | |||
5 | Kim | Sanku | Senior | Peanut | Kisama | Senior | |||
6 | Peanut | Thor | Cookie | Kim | Thor | Puk | |||
7 | Puk | Momo | Puk | Kisama | Kuku | Thor | |||
8 | Luffy | Peanut | Thor | Sanku | Puk | Luffy | |||
9 | Senior | Kim | Luffy | Momo | Luffy | Tony | |||
10 | Kuku | Kuku | Tony | Kuku | Loki | Loki | |||
11 | Momo | Kisama | Loki | Senior | Peanut | Kisama | |||
12 | Sanku | Keke | Kisama | Luffy | Sanku | Momo | |||
13 | Kisama | Puk | Momo | Puk | Tony | Kim | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F13 | F2 | =SORTBY(E2:E13,RANDARRAY(ROWS(E2:E13))) |
Dynamic array formulas. |
here's the macro i'm using to assign cells:
Sub Fill_Blanks_From_List2()
Dim StartRow As Long
Dim rA As Range
StartRow = 2
For Each rA In Range("B2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
rA.Value = Range("G" & StartRow).Resize(rA.Count).Value
rA.Font.Color = vbRed
StartRow = StartRow + rA.Count
Next rA
End Sub
Sub Fill_Blanks_From_List3()
Dim StartRow As Long
Dim rA As Range
StartRow = 2
For Each rA In Range("C2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
rA.Value = Range("G" & StartRow).Resize(rA.Count).Value
rA.Font.Color = vbRed
StartRow = StartRow + rA.Count
Next rA
End Sub