Putting words into categories and showing the category rather than the word in each cell

[Siobhan-]

Hello, I am wanting to know how to put words into categories. So for example if I have 3 different lists of words I want them to be assigned either category 1, 2, or 3. 

And then rather than the word showing up in the cell I would like to change this to the category number. So If I believe right, I need to code the words to show as their category number. For example, if the word "verdict" is in category 1 then show "1" instead of "verdict", if the word "justice" is in category 2 then show "2" instead of "justice". How would I put my word lists into categories and then show that category number in the cell rather than the word item. 

I am new to excel and this is really important as it is for my masters experiment -  I need to be able to correlate the written responses but I cannot do this with words so I need to change the words to numbers. - any help will be very appreciated. 

Thank you, Siobhan! 

[#2]

@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. 

[#3]

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! 

[#4]

@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.

[#5]

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 :) 

[#6]

@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?

[#7]

Morning @Riny_van_Eekelen. Sorry for the confusion! I'm not sure what you mean by word item and response being separated by a line feed, sorry I am new to excel! But on sheet 3 in the response section where the 3 responses are all in one cell, this is how the responses show up on excel from the data. Sheet 1 is how PsychoPY automatically downloads the data, but I do not need all of the information on that sheet for example "info key" or "welcome screen" I literally just need the participants response, so the following sheets are where I have edited all of the other data out. - It doesn't relate at all to the word stimuli sheet as the combined one was just a practice to see how the data shows up, the F and R are elements of the research which can just be ignored if that's okay:) - unless you need to know what they are of course. But I'm hoping to not have to have the "R"s and "F"s on the stimuli list, going to try find an alternative way around it in PsychoPY. - Say if the practice experiment was the real one, the stimuli which is the worditem, is what is being asked to recall in the test afterwards and the response cells are what they have recalled - these responses all go into one cell, I wish to know how I can make it so that the stimuli (word item) is grouped into 3 groups 1,2,3, and then take on that numerical value instead of the written word so that when I have my real data I can somehow change the written responses to fit in line with the 1,2,3. -e.g. say chair is a wordItem, and in the response cell someone has wrote chair, I would want the word item to be 1 instead of chair and then the response of chair to also take on that number, that way I am working with numbers and I can still establish what word group the word has come from (individual words aren't needed, it is more the group they belong to). I apologise for the lengthy message, I wanted to try and explain for you to gain clarification! Thank you for your time! I really appreciate it:) 

[#8]

@Siobhan- Line-feeds are invisible characters and cause the responses to be display on separate rows within one cell.

Regarding Sheet3, are the three words (like in A2) always belonging to the same group, e.g. 1, 2 or 3? If so, you can write a formula like in G2 and G3 in the attached workbook. It will extract the first word and looks up the corresponding value from the table in Sheet4.

[#9]

@Riny_van_Eekelen Oh I see! The responses will always show in one cell as this is how PsychoPY converts the responses into data. The neutral words will always belong to a specific group, same with the other word list groups but some of the responses won't always be in the same group for example, people could recall words from group 1, 2 and 3. So with the example sheet I would want the words table, carpet and chair in one group and the words feeling, cry and love in another group, both groups with different numerical value that attaches to the responses. So in the response cell where there is "chair, carpet, table" all of those words would belong to group 1 and therefore show as "1"s rather than the word. But for the actual experiment excel sheet it will need 3 different groups of words and the responses to align with the 3 different numbers of the groups. I feel like this may be impossible to do! 

Thank you! Siobhan:)

[#10]

@Siobhan- I guess you need to split the responses into separate words so that you can lookup the corresponding value for each of them. Possible, but you it would be helpful if you could upload a sample of the file from PsychoPY?

[#11]

@Riny_van_Eekelen Yes that makes perfect sense, It will be a lengthy process but if that is what I have to do then so be it! I just don't know how to attach a value onto the words so that they can show up as a number rather than the word... The combined excel file is all of the practice PsychoPY experiment data (In the combined sheet), I shall however attach a single data set too! If it would be more helpful I can create my actual experiment and use the actual word stimuli, this will take me a while though so I wouldn't be able to attach any data from it today! 

Thanks, Siobhan:) 

[#12]

@Siobhan- So, file you attached is how it always looks like (i.e. column order/headers) and the cell att the bottom in column X is what? Is this the respons for participant 1?

I'm sure it's all clear in your head, but not in mine. Based on the data set, what needs to be matched with what and with what logic? Sorry, if I'm slow!

[#13]

@Riny_van_Eekelen The formation of the data file depends on how I make the experiment, so the data file for that practice one will not look the same as the data file for my real one but it is generally the same. It will just have different wordItems and responses and the responses may be in a different column. Yes column X is the responses of the participant, so I would want to assign the wordItems into categories and each category have a numerical value that can be attached on to the responses, so that in the column for the responses it will detect what category the word belongs to and then assigns it the same numerical value as the category. This is in order for me to correlate the data, as I can't conduct statistical analysis with the words. 

Basically, I would need each response to change to either 1,2,3 based on which category they come from so that I can create a pivot table or use Jamovi for example to see how many times someone recalled a word from a specific category, and to do this I believe I will have to change the word to a number! And no need for apologies, its totally fine, I am sorry if I am confusing you! 

[#14]

@Siobhan- As long as the structure of the data is the same, it shouldn't be too difficult. You'll have a list of words, each assigned to a group number. And you have responses in a slightly odd way. I feel we are over-thinking here. Can you, on the basis of your file, show how you want to summarise the data? And best to use an example that is representative for a real life situation. 

[#15]

@Riny_van_Eekelen Have added some instructions to your file in the shaded area below the table. Hopefully, it enables you to clean up the participants' responses and find the word groups for further analysis.

[#16]

@Riny_van_Eekelen Thank you so much! I will follow the instructions and have a go! I really appreciate your help :)