multiple column find and replace

[JuniorS]

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

[JosephSerpis · Accepted solution]

Hi @JuniorS I mocked up a workflow I believe answers your question let me know what you think?

[ashissanpui · Accepted solution]

@JuniorS  - I think you need to rearrange your data. I have attached the workflow as a solution.

[EW · Accepted 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.