Extract from Survey Data

[CassieL]

Hi everyone, I have a huge question survey data and I was told to summarize the survey and give useful information. However, there is to many column (question)(from column T to RTG) with participants of 49. I hope you guys can guide me the best way to get the information.
A million thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Participants[/TD]
[TD]which acct you support?-ACC[/TD]
[TD]which acct you support? - BCC[/TD]
[TD]ccc[/TD]
[TD]dcc[/TD]
[TD]financial system - e1[/TD]
[TD]financial system- world?[/TD]
[TD]pay rent?[/TD]
[TD]utilities[/TD]
[TD]how much ?[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

Above are the example the survey was created. ( it reads by horizontally) I like to summarize by the account. For example
Account: ACC
financial system: E1
pay rent: yes

A MILLION OF THANKS and your help will greatly appreciated!
how much: 4000

 

[mole999]

I would be tempted to make a named range then model in pivot table, where you can select and filter the data to suit what you are looking at

 

[CassieL]

Would you filter by account?

 

[mole999]

would depend on what you wanted to model

 

[Mike Szczesny]

Can you use something like this? I realize that is not exactly what you are looking for. At least this formula will show only activity for a specific participant. Hope this helps.

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1		ACC	BCC	CCC	DCC	E1	World	Rent	Utilities	Amount
2	a	1				1		1		4000
3	b		1				1		1	200
4	c			1		1		1		6000
5	d				1		1		1	500
6
7
8
9	a	ACC	E1	Rent
10					4000
Sheet1

Cell Formulas
Range	Formula
B9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:B8)))," ")}
C9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:C8)))," ")}
D9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:D8)))," ")}
E9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:E8)))," ")}
F9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:F8)))," ")}
G9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:G8)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mike Szczesny]

For some reason, this formula for the amount got cut off when I pasted this.

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1		ACC	BCC	CCC	DCC	E1	World	Rent	Utilities	Amount
2	a	1				1		1		4000
3	b		1				1		1	200
4	c			1		1		1		6000
5	d				1		1		1	500
6
7
8
9	a
10					4000
Sheet1

Cell Formulas
Range	Formula
E9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:E8)))," ")}
F9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:F8)))," ")}
G9	{=IFERROR(INDEX($B$1:$I$1,SMALL(IF($A$2:$A$5=$A$9,IF($B$2:$I$5=1,COLUMN($B$2:$I$5)-COLUMN($B$2)+1)),COLUMNS($B$8:G8)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.