Forum Discussion
Putting words into categories and showing the category rather than the word in each cell
Siobhan- Put all your words in one column and the number you want to attach to it in the one next to it. Then you can use one of Excels lookup functions (XLOOKUP, VLOOKUP or INDEX/MATCH) to look-up the word and returns its number.
Hi Riny_van_Eekelen , thank you for this! I have done as you said with the words and the number I want to attach to them but when I try the lookup functions it is saying incorrect formula. I have just watched a tutorial to change them using a replace function and that has replaced the word with the number but only in the replace column and not in my word column.
Essentially I think I need to assign my 3 different word lists to 3 different numbers, so that when participants write down a specific word from say category 1, excel will show 1 instead of the word.
Thank you for your help!
- Riny_van_EekelenJan 20, 2021Platinum Contributor
Siobhan- Perhaps the attached workbook will help you solve your problem. If not, please upload an example of your workbook so that we can have a look at it.
- Siobhan-Jan 20, 2021Copper Contributor
Good evening, Riny_van_Eekelen. Thank you again! And thank you so much for making that workbook and giving up your time! Unfortunately, it is not working for me when I use those formulas. I'll attach the workbook with my word stimuli, there are 3 different categories of words as you will see each word list has 36 words and I need the "neutral" words to show as 1, "emotional" words as 2 etc.
Essentially, I am conducting a memory recall test so these words will show on the screen and afterwards each participant will need to write as many words as they can remember and these written responses show in one cell and as text format but I need to somehow assign the worded responses to a number so I can correlate the responses. I thought giving each category of words a number would help me do this but I have been trying to figure it out all day and no luck! I have made a practice experiment using less words to see how the data file would look. I shall attach that too so you can get a general idea of what my data will look like in the real experiment. - On the third sheet of the combined_recall file I have gave a brief explanation of what I mean.
Thanks again! Kind regards, Siobhan 🙂
- Riny_van_EekelenJan 21, 2021Platinum Contributor
Siobhan- Thanks for the example sheets, but I'm not really following your intentions.
E.g. on Sheet3 in the Combined file, why are the three words (Item and Response) in one cell, separated by a line feed? On Sheet1, what is the meaning of RRRR or FFFF attached to each word in column B? On the sheet named "combined", how do all the numbers relate to Sheet1 and/or Sheet3?