Please help 🙂
How do I dynamically replace a lot of fields so I have table one with each subject and difficulty rating and I want to be able to calculate an overall difficulty average for each student :
table 1:
mathtype | math difficulty | English type | English difficulty | art type | art difficulty | musictype | music difficulty |
a | 9 | e | 9 | a | 10 | x | 9 |
b | 6 | f | 6 | b | 8 | y | 6 |
c | 5 | g | 4 | c | 6 | z | 3 |
d | 4 | d | 4 |
table two :
student | mathtype | english | art type |
aa | a | e | b |
bb | a | f | d |
cc | d | g | a |
dd | a | e | a |
gg | c | g | d |
I want to a be able to say Student aa has , difficulties 9 + 9 + 8 without having to do multiple find and replace tools for each subject type to subject difficulty . how would a dynamic replace work for this, to give me table two with difficulties for each type.
Thank you
Gelöst! Gehe zu Lösung.
@JuniorS - I think you need to rearrange your data. I have attached the workflow as a solution.
One way is to transpose each data set, and use crosstab and join to bring them back together like the attached.
Note that I edited the headers from your example to make them consistent and parse-able - if that's not a good option, you may need to add data cleansing for a formula to make them consistent.