Sum of Questionnaire Scores Based on a Domain Table

[herman925]

I have created a questionnaire that consists of around 100 questions. These items are separated into 6 domains where. For the sake of easier understanding, let's just call them Domain 1 - 6.

I have them typed in one specific table called "Correspondence", with format like below:

Question No.	Domain
1	A
2	C
3	A
4	B
5	A
6	C

<tbody>
</tbody>

I used Google Form to generate a spreadsheet of RAW data of respondents, where it will help me mark the RAW Scores, for each item on a separate column:

(An example)

Submission ID	Question 1	Question 2	Question 3	Question 4	Question 5	Question 6
Participant 1	2	3	5	1	2	4
Participant 2	5	4	5	3	5	1
Participant 3	1	1	1	2	2	2

<tbody>
</tbody>

The next thing I need to do is generate another table that sums up the Domain totals for each participant. So from the example above, I need to sum 1,3,5 as Domain A, 4 as Domain B and 2 & 6 as Domain C:

(An example)

	Domain A	Domain B	Domain C
Total score for Participant 1	9	1	7

<tbody>
</tbody>

The hardest thing is to find a proper method to kick start this process. Can anyone point me in the right direction? Either formulas or VBAs would be fine too. Thanks!

 

[sandy666]

something like this or I misunderstood ?

Submission ID	A	B	C
Participant 1	9​	4​	8​
Participant 2	9​	4​	8​
Participant 3	9​	4​	8​

 

[sandy666]

ignore my previous post#2

here is with PowerQuery:

Submission ID	A	B	C
Participant 1	9​	1​	7​
Participant 2	15​	3​	5​
Participant 3	4​	2​	3​

[SIZE=1]let
    Source = Table.NestedJoin(Table2,{"Attribute"},Table1,{"Question No."},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Domain"}, {"Domain"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table1", {"Submission ID", "Domain"}, {{"Value", each List.Sum([Value]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Domain", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Domain]), "Domain", "Value", List.Sum)
in
    #"Pivoted Column"[/SIZE]

but before execute M-code above you need to prepare both tables of source

Question No.	Domain		Submission ID	Attribute	Value
Question 1	A		Participant 1	Question 1	2​
Question 2	C		Participant 1	Question 2	3​
Question 3	A		Participant 1	Question 3	5​
Question 4	B		Participant 1	Question 4	1​
Question 5	A		Participant 1	Question 5	2​
Question 6	C		Participant 1	Question 6	4​
			Participant 2	Question 1	5​
			Participant 2	Question 2	4​
			Participant 2	Question 3	5​
			Participant 2	Question 4	3​
			Participant 2	Question 5	5​
			Participant 2	Question 6	1​
			Participant 3	Question 1	1​
			Participant 3	Question 2	1​
			Participant 3	Question 3	1​
			Participant 3	Question 4	2​
			Participant 3	Question 5	2​
			Participant 3	Question 6	2​

 

[herman925]

The RAW data will be constantly modified and not maintained by me and thus I am not sure if I could change the format of the RAW table to the one you specified.....but appreciate the thought!

 

[sandy666]

you can change data in source table as long as format and type of data will be the same in appropriate places

changes was maded in PowerQuery - your source table is still the same as in your post