HI guys, need your help
I have 2 table, prediction table where users make prediction on a game and on exact final result and another table with the real final results.
I need to assign 3 point to a user if his prediction is correct and another 3 points if also result is correct.
Any idea how to match the 2 tables?
Thanks
Prediction table
User | D01 - Prediction | D01 - Result | D02 - Prediction | D02 - Result | D03 - Prediction | D03 - Results | …. | …. |
Paul | 1 | 2-0 | 2 | 0-2 | …. | …. | …. | …. |
Robert | x | 0-0 | x | 1-1 | …. | …. | …. | …. |
Andrew | 1 | 1-0 | 1 | 1-0 | …. | …. | …. | …. |
Peter | 1 | 2-1 | 2 | 1-3 | …. | …. | …. | …. |
Angel | 2 | 0-2 | x | 2-2 | …. | …. | …. | …. |
Real Result
Game | Exact Prediction | Exact Result |
D01 | 1 | 2-1 |
D02 | x | 3-3 |
D03 | 1 | 2-0 |
…. | …. | …. |
Solved! Go to Solution.
Just a new field with final score for each user
As i said 3 point for any exact prediction and more 3 point for any exact result
Hi @AnGi
Here is how you can do it.
Workflow:
1. For prediction table keeping user as key convert columns to rows
2. For Game table keeping user as key convert columns to rows
3. Using formula tool creating a matching game name (like D01 - Prediction)
4. Using find & replace to do vlookup on Game & type
5. If the value of prediction table is equal to results table then 3 else 0.
6. Using crosstab to make it a table (this is optional step)
Hope this helps 🙂