Hey Everyone,
I was hoping I could get a little help with a small project I was trying to work on for fun. I’m trying to come up with a series of 20 four letter words that have similar letters in order to create a kind of “Mastermind” guessing game. Kind of similar to the Hacking minigame in the Fallout series works. The idea is you are trying to guess the correct word. After picking one of the 20 words, you’d then see how many letters are in the correct spot. You won’t know which letters are correct, just that 0, 1, 2, or 3 letters are correct out of 4.
For example, if the secret word, is FEAR and you guess TIRE, none of the letters are in the correct spot. So, you’ll see 0. If your next guess is FAIL, you’ll see a 1 because of the F. Next guess might be DEAR, so you’ll see 3. And so on, eventually allowing you to narrow it down to the one correct word.
The problem is I don’t know a good array of words that would make for a good guessing puzzle. To try and help, I was hoping to create an excel spreadsheet that would at least give me an idea of what words have a certain amount of matching letters. I just don’t know how to create a function that compares multiple cells and keeps track of how many cells in multiple rows match specified column cells.
What I was trying to do is create a 4x20 table of cells with letters in each cell. Each row would then spell a 4 letter word. There would be a table next to it that then keeps track of how many of the other rows contain words that have 0, 1, 2, and 3 matching letters in the same columns.
In the picture I attached, I have 20 rows of words just as examples. B2 to E21 contains letters that spell out 20 different words. To try and explain what I mean, I started with the top row, B2:E2, which spells FEAR and compared all the other rows. I put how many letters are in the correct spot along the A column. So, B3:E3 has FAIL, only the F is in the correct spot, so I put a 1. B4:E4 is FOOT, so it’s also only the F that’s correct. B6:E6 has SEAL in the row, because the E and the A are in the correct spot I put a 2 in the A column.
Then, in columns F through I, I added up how many words had 0, 1, 2, and 3 matches. Basically, how many 0’s, 1’s, 2’s, and 3’s are in Column A. The problem is that it would take forever to do every single word manually. Especially if I wanted to try doing 5 or 6 letter words. So, I was hoping there might be a simple formula for doing all this automatically? If the formula worked correctly, cell K2 would have the number 9 in it, cell L2 would have a 7, M2 would have a 2, and N2 would have a 1.
I’m pretty new to excel, so I don’t know all the capabilities. I was thinking maybe a Countif or Countifs function could work, but that just counts all the cells that match the value. I don’t know how to count cells that don’t match the value. Plus, I don’t want to keep a tally of how many times ANY of the letters match in the entire word. I need it to compare each cell of each row to letters in the same columns of each of those other rows. I was thinking maybe combining Countif with an IF statement to try and first check if the row has a matching letter in the correct column, then adding 1 to a tally, then do it again with the second letter of the next column, and so on. But I feel like that function would be dozens of lines long having separate functions to check each and every cell individually. That might be more work than just doing it all manually. I don’t know how to do just a sweeping check of an entire column and counting separately for each row. If you get what I mean? And how would that translate to maybe a 5x20 table or 6x20 table?
Ideally it would be comparing text in a specified cell and not just a specific letter too. The idea being that I could change a letter in one of the cells and it would automatically update all of the counts. Maybe instead of an S in the cell B6 to spell SEAL, I want to change it to a D, making DEAL. That would have a pretty big cascade effect on the matching counts of all the other words, which would take forever to go back through manually and redo all the numbers. So, it’d be nice if there were formulas that did it automatically.
I hope all of that wasn’t super confusing. If you have any questions or need clarification, just let me know and I’ll do my best. Any ideas or help would be greatly appreciated.
Thanks,
Dave
I was hoping I could get a little help with a small project I was trying to work on for fun. I’m trying to come up with a series of 20 four letter words that have similar letters in order to create a kind of “Mastermind” guessing game. Kind of similar to the Hacking minigame in the Fallout series works. The idea is you are trying to guess the correct word. After picking one of the 20 words, you’d then see how many letters are in the correct spot. You won’t know which letters are correct, just that 0, 1, 2, or 3 letters are correct out of 4.
For example, if the secret word, is FEAR and you guess TIRE, none of the letters are in the correct spot. So, you’ll see 0. If your next guess is FAIL, you’ll see a 1 because of the F. Next guess might be DEAR, so you’ll see 3. And so on, eventually allowing you to narrow it down to the one correct word.
The problem is I don’t know a good array of words that would make for a good guessing puzzle. To try and help, I was hoping to create an excel spreadsheet that would at least give me an idea of what words have a certain amount of matching letters. I just don’t know how to create a function that compares multiple cells and keeps track of how many cells in multiple rows match specified column cells.
What I was trying to do is create a 4x20 table of cells with letters in each cell. Each row would then spell a 4 letter word. There would be a table next to it that then keeps track of how many of the other rows contain words that have 0, 1, 2, and 3 matching letters in the same columns.
In the picture I attached, I have 20 rows of words just as examples. B2 to E21 contains letters that spell out 20 different words. To try and explain what I mean, I started with the top row, B2:E2, which spells FEAR and compared all the other rows. I put how many letters are in the correct spot along the A column. So, B3:E3 has FAIL, only the F is in the correct spot, so I put a 1. B4:E4 is FOOT, so it’s also only the F that’s correct. B6:E6 has SEAL in the row, because the E and the A are in the correct spot I put a 2 in the A column.
Then, in columns F through I, I added up how many words had 0, 1, 2, and 3 matches. Basically, how many 0’s, 1’s, 2’s, and 3’s are in Column A. The problem is that it would take forever to do every single word manually. Especially if I wanted to try doing 5 or 6 letter words. So, I was hoping there might be a simple formula for doing all this automatically? If the formula worked correctly, cell K2 would have the number 9 in it, cell L2 would have a 7, M2 would have a 2, and N2 would have a 1.
I’m pretty new to excel, so I don’t know all the capabilities. I was thinking maybe a Countif or Countifs function could work, but that just counts all the cells that match the value. I don’t know how to count cells that don’t match the value. Plus, I don’t want to keep a tally of how many times ANY of the letters match in the entire word. I need it to compare each cell of each row to letters in the same columns of each of those other rows. I was thinking maybe combining Countif with an IF statement to try and first check if the row has a matching letter in the correct column, then adding 1 to a tally, then do it again with the second letter of the next column, and so on. But I feel like that function would be dozens of lines long having separate functions to check each and every cell individually. That might be more work than just doing it all manually. I don’t know how to do just a sweeping check of an entire column and counting separately for each row. If you get what I mean? And how would that translate to maybe a 5x20 table or 6x20 table?
Ideally it would be comparing text in a specified cell and not just a specific letter too. The idea being that I could change a letter in one of the cells and it would automatically update all of the counts. Maybe instead of an S in the cell B6 to spell SEAL, I want to change it to a D, making DEAL. That would have a pretty big cascade effect on the matching counts of all the other words, which would take forever to go back through manually and redo all the numbers. So, it’d be nice if there were formulas that did it automatically.
I hope all of that wasn’t super confusing. If you have any questions or need clarification, just let me know and I’ll do my best. Any ideas or help would be greatly appreciated.
Thanks,
Dave
