I'm working with a survey dataset that needs to be aggregated for analysis purposes. Right now I'm looking to identify all instances where different respondents answered a question with the same numeric answer from a multiple choice list, the number of respondents who answered with the same answer, and then a list of the numerical values that they answered with.
So basically, right now I have a data table that identifies which numerical value each respondent (total of 9 respondents) responded with for each question, with the numerical values ranging from 1-42.
Right now I have a table that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]BK
[/TD]
[TD]BL
[/TD]
[TD]BM
[/TD]
[TD]BN
[/TD]
[TD]BO
[/TD]
[TD]BP
[/TD]
[TD]BQ
[/TD]
[TD]BR
[/TD]
[TD]BS
[/TD]
[TD]BT
[/TD]
[TD]BU
[/TD]
[TD]BV
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Total
[/TD]
[TD]Respondents in Support
[/TD]
[TD](formula in this column looks at data table in S2:BH12 to identify how many of the numerical values had X
[listed in column BK] supporters)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]Respondents
[/TD]
[TD]0
[/TD]
[TD]=IF($BM3<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK3,$S$12:$BH$12,0))) -- answer is 0, as BM3 is less than BN2
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0)))<strike></strike> -- answer is 19 (the matching header from the first column in the dataset that states "8" as the subtotal)
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7
[/TD]
[TD]Respondents
[/TD]
[TD]2
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0))) -- answer is 18 (the matching header from the first column in the dataset that states "7" as the subtotal)
[/TD]
[TD]HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 7 in subtotal of the dataset?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM6<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK6,$S$12:$BH$12,0)))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM7<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK7,$S$12:$BH$12,0)))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4
[/TD]
[TD]Respondents
[/TD]
[TD]3
[/TD]
[TD]=IF($BM8<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK8,$S$12:$BH$12,0)))
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM9<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK9,$S$12:$BH$12,0)))
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM10<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK10,$S$12:$BH$12,0)))
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Of course, there's like 4 separate posts on how to do this on ExcelJet, but it seems to be down for me no matter what computer I visit it from...
So basically, right now I have a data table that identifies which numerical value each respondent (total of 9 respondents) responded with for each question, with the numerical values ranging from 1-42.
Right now I have a table that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]BK
[/TD]
[TD]BL
[/TD]
[TD]BM
[/TD]
[TD]BN
[/TD]
[TD]BO
[/TD]
[TD]BP
[/TD]
[TD]BQ
[/TD]
[TD]BR
[/TD]
[TD]BS
[/TD]
[TD]BT
[/TD]
[TD]BU
[/TD]
[TD]BV
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Total
[/TD]
[TD]Respondents in Support
[/TD]
[TD](formula in this column looks at data table in S2:BH12 to identify how many of the numerical values had X
[listed in column BK] supporters)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]Respondents
[/TD]
[TD]0
[/TD]
[TD]=IF($BM3<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK3,$S$12:$BH$12,0))) -- answer is 0, as BM3 is less than BN2
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0)))<strike></strike> -- answer is 19 (the matching header from the first column in the dataset that states "8" as the subtotal)
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7
[/TD]
[TD]Respondents
[/TD]
[TD]2
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0))) -- answer is 18 (the matching header from the first column in the dataset that states "7" as the subtotal)
[/TD]
[TD]HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 7 in subtotal of the dataset?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM6<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK6,$S$12:$BH$12,0)))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM7<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK7,$S$12:$BH$12,0)))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4
[/TD]
[TD]Respondents
[/TD]
[TD]3
[/TD]
[TD]=IF($BM8<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK8,$S$12:$BH$12,0)))
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 4 in subtotal of the dataset?
<strike></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 4 in subtotal of the dataset?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM9<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK9,$S$12:$BH$12,0)))
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 3 in subtotal of the dataset?
<strike></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 3 in subtotal of the dataset?
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FOURTH INSTANCE OF 3 in subtotal of the dataset?
<strike></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FIFTH INSTANCE OF 3 in subtotal of the dataset?
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SIXTH INSTANCE OF 3 in subtotal of the dataset?
<strike></strike>[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM10<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK10,$S$12:$BH$12,0)))
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 2 in subtotal of the dataset?
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 2 in subtotal of the dataset?
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FOURTH INSTANCE OF 2 in subtotal of the dataset?
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FIFTH INSTANCE OF 2 in subtotal of the dataset?
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SIXTH INSTANCE OF 2 in subtotal of the dataset?
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Of course, there's like 4 separate posts on how to do this on ExcelJet, but it seems to be down for me no matter what computer I visit it from...