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

J

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

  • ExcelDemy.xlsx
    23.4 KB · Views: 2
Jululian said:
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
Click to expand...
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)))), "")
    Click to expand...
  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)))), ""))
    Click to expand...
  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

  • ExcelDemy.xlsx
    23.3 KB · Views: 2
Lutfor Rahman Shimanto said:
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
Click to expand...
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

  • Book2.xlsx
    22.1 KB · Views: 1
Last edited:
Jululian said:
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
Click to expand...
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))
    Click to expand...
  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

  • Jululian (SOLVED).xlsm
    31.3 KB · Views: 1
Lutfor Rahman Shimanto said:
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
Click to expand...
The support you have given me is greatly appreciated and your assistance has had a significant impact thank you and your team
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
36
Total visitors
36
Totals may include hidden visitors.

Forum statistics

Threads
336
Messages
1,469
Members
624
Latest member
duytoi

Share this page

Top