I have a table called applicant_acctg_exam with 14 columns named question1, question2,… question14.
Each column has an initial value which is formulated. The formula is to randomly select from the table gpt_accounting1.
I want have to achieve here is that each column from the table applicant_acctg_exam should be randomly selected from the table gpt_accounting1 distinct from each other. Meaning the 14 columns of table applicant_acctg_exam should be unique. Is this possible? Thanks
Right now, Im currently using this formula. But this doesnt give a unique value to 14 columns.
INDEX(GPT_accounting1[question], RANDBETWEEN(1, COUNT(GPT_accounting1[question])))
try this
SELECT(QUESTION[Column_Name], NOT(IN([Column_Name],
EXAM[Q1], EXAM[Q2], EXAM[Q3], EXAM[Q4], EXAM[Q5],
EXAM[Q6], EXAM[Q7], EXAM[Q8], EXAM[Q9], EXAM[Q10], EXAM[Q11], EXAM[Q12], EXAM[Q13], EXAM[Q14])))
Replace “QUESTION” with the actual name of your “QUESTION” table. Also, make sure to adjust the column names (Q1, Q2, …, Q14) to match the column names in your “EXAM” table.
My apology for using another table/column for using a illustration for asking a question. But this is the output. It’s error.
To correct : the table “gpt_accounting1” is the table where the 14 questions located. and table “applicant_acctg_exam” is where the 14 columns located.
Let’s assume you have 100 questions (rows) in your Questions table. The first column would need something like.. INDEX(Questions[ID],RANDBETWEEN(1,100)).
The next column would need to have..
INDEX(SELECT(Questions[ID],NOT(IN([ID],LIST([_THISROW].[Q1])))),RANDBETWEEN(1,99)) and so on..
@chiukim
SELECT(gpt_accounting1[question],
NOT(IN([question],
LIST(
SELECT(applicant_accte_exam[question1]),
SELECT(applicant_acctg_exam[question2]),
SELECT(applicant_acctg_exam[question3]),
SELECT(applicant_acctg_exam[question4]),
SELECT(applicant_acctg_exam[question5])
)
)))
Thanks. But this doesnt work.
It doesnt work. It creates a list.
It doesn’t work in what way?
its doesnt unique the initial value.
I would like to clarify your suggestions. the 1st formula is the initial value of the question 1 and the 2nd formula is the initial value of question 2?
Correct. If you use the formula with the initial value, it only works when creating the new row, not when you modify it. The formula I wrote, should do the job.
INDEX(SELECT(Questions[ID],NOT(IN([ID],LIST([_THISROW].[question2])))),RANDBETWEEN(1,12))
Please confirm if this is the initial value for 3rd column.
The [_thisrow]. [question2] is minus 1 ?
Ive already tried your formula. but it still get the same value.
The 3rd one needs..
INDEX(SELECT(Questions[ID],NOT(IN([ID],
LIST(
[_THISROW].[question1],
[_THISROW].[question2])
))),RANDBETWEEN(1,12))
