Dear

**Jululian**
Thanks for sharing your requirements. I reviewed your problem and found that developing such formulas using only Excel's built-in function is extremely difficult and time-consuming. So, I have created an

**Excel VBA User-defined** function to fulfil your goal. Later, use the

**TRANSPOSE** function and call the User-defined function within it.

**SOLUTION Overview**:

**Follow these steps**:

- Go to the
**Developer** tab, followed by **Visual Basic**.
- Click on
**Insert** followed by **Module**.
- Paste the following code in the module and save it:

Code:

```
Function RandomizeSubjects(rng As Range) As Variant
Dim subjects() As String
Dim randomizedSubjects() As String
Dim tempArray() As String
Dim i As Integer, j As Integer, randIndex As Integer
Dim IslamicIndex As Integer
Dim subjectCount As Integer
subjectCount = rng.Cells.Count
ReDim subjects(1 To subjectCount)
For i = 1 To subjectCount
subjects(i) = rng.Cells(i, 1).Value
Next i
ReDim tempArray(1 To subjectCount - 1)
j = 1
For i = 1 To subjectCount
If subjects(i) <> "Islamic education 2" Then
tempArray(j) = subjects(i)
j = j + 1
End If
Next i
Randomize
For i = 1 To UBound(tempArray)
randIndex = Int((UBound(tempArray) - 1 + 1) * Rnd + 1)
Dim temp As String
temp = tempArray(i)
tempArray(i) = tempArray(randIndex)
tempArray(randIndex) = temp
Next i
For i = 1 To UBound(tempArray)
If tempArray(i) = "Islamic education 1" Then
IslamicIndex = i
Exit For
End If
Next i
ReDim randomizedSubjects(1 To subjectCount)
j = 1
For i = 1 To UBound(tempArray)
If i = IslamicIndex Then
randomizedSubjects(j) = "Islamic education 1"
j = j + 1
randomizedSubjects(j) = "Islamic education 2"
j = j + 1
Else
If tempArray(i) <> "Islamic education 1" Then
randomizedSubjects(j) = tempArray(i)
j = j + 1
End If
End If
Next i
RandomizeSubjects = randomizedSubjects
End Function
```

View attachment 1426
- Now, return to the sheet and select the intended cell.
- Apply the following formula:
- Press
**Enter**.

View attachment 1427

Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

Regards

**Lutfor Rahman Shimanto**

Excel & VBA Developer

ExcelDemy