[Solved] Lookup Table Formula to Insert Subject Names Instead of Numbers

Jululian

Member
Dear all
Your help, as per the attached Excel file, would be very much appreciated, and I appreciate the support you provide.
I want to express my gratitude in advance
Regards
George Jululian
 

Attachments

Dear all
Your help, as per the attached Excel file, would be very much appreciated, and I appreciate the support you provide.
I want to express my gratitude in advance
Regards
George Jululian
Dear Jululian

It is good to see you again. Thanks for your nice words.

I have reviewed your requirements, which are described in the attached Excel file. Though it seems like a day-killer problem, I am able to solve it somehow with the help of a helper column (within this range, the text font is white). Please check the following:

if the number in the main table is 5 to give instead Chemistry or if the number 3 to give Scie...gif

Follow these steps:
  1. Select cell E7.
  2. Insert the following formula:
    =IF(ROW(1:1)<11, LARGE((COUNTIF($F$6:F6, ROW($1:$10))=0)*ROW($1:$10), RANDBETWEEN(1, SUM(--(COUNTIF($F$6:F6, ROW($1:$10))=0)))), "")
  3. Hit Enter to get the output like:
    Select cell E7 and apply the given formula.png
  4. Now, choose cell E8.
  5. Apply the following formula:
    =IF(E7=9,10,IF(ROW(2:2)<11, LARGE((COUNTIF($E$7:E7, ROW($1:$10))=0)*ROW($1:$10), RANDBETWEEN(1, SUM(--(COUNTIF($E$7:E7, ROW($1:$10))=0)))), ""))
  6. Drag the Fill Handle icon to copy the formula down.
    Select cell E8, apply the given formula and drag the fill handle icon to copy the formula down.png
I have attached the solution workbook as well; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 

Attachments

Dear Jululian

It is good to see you again. Thanks for your nice words.

I have reviewed your requirements, which are described in the attached Excel file. Though it seems like a day-killer problem, I am able to solve it somehow with the help of a helper column (within this range, the text font is white). Please check the following:


Follow these steps:
  1. Select cell E7.
  2. Insert the following formula:
  3. Hit Enter to get the output like:
    View attachment 1418
  4. Now, choose cell E8.
  5. Apply the following formula:
  6. Drag the Fill Handle icon to copy the formula down.
    View attachment 1419
I have attached the solution workbook as well; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy
Your assistance with my criteria has been greatly appreciated and has had a significant impact. Thank you
 
Is it possible to wrap the formula in cell G7 ONLY with a lookup table without any criteria for inserting subjects instead of numbers?
Your assistance in this matter is greatly appreciated and has had a significant impact. Thank you
 

Attachments

Last edited:
Is it possible to wrap the formula in cell G7 ONLY with a lookup table without any criteria for inserting subjects instead of numbers?
Your assistance in this matter is greatly appreciated and has had a significant impact. Thank you
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:

Wrapping a user-defined function combined with the TRANSPOSE function in cell G7 ONLY with a l...gif

Follow these steps:
  1. Go to the Developer tab, followed by Visual Basic.
  2. Click on Insert followed by Module.
  3. 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
    Open VBA Editor, paste the given code in the module and save it.png
  4. Now, return to the sheet and select the intended cell.
  5. Apply the following formula:
    =TRANSPOSE(RandomizeSubjects(C8:C17))
  6. Press Enter.
    Choose the intended cell, apply the given formula and hit Enter.png

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
 

Attachments

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:
  1. Go to the Developer tab, followed by Visual Basic.
  2. Click on Insert followed by Module.
  3. 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
  4. Now, return to the sheet and select the intended cell.
  5. Apply the following formula:
  6. 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
The support you have given me is greatly appreciated and your assistance has had a significant impact thank you and your team
 

Online statistics

Members online
0
Guests online
6
Total visitors
6

Forum statistics

Threads
382
Messages
1,674
Members
723
Latest member
Odion
Back
Top