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!

Participants	which acct you support?-ACC	which acct you support? - BCC	ccc	dcc	financial system - e1	financial system- world?	pay rent?	utilities	how much ?
a	1				1		1		4000
b		1				1		1	200
c			1		1		1		6000
d				1		1		1	500

<tbody>
</tbody>

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.