Count if data is present in any of n fields

[Anonymous]

Hi,

I have some data where people have qualifications listed in two fields:

Name	Qualification 1	Qualification 2	Qualification ......	Qualification N
Alice	A	B	...	C
Brian	B	C	...	X
Charles	C	A	...	Z
Dave	A	D	...	W
Emily	D	F	...	A
Frank	F	X	...	C
Gary	F	Z	...	J
Harry	G	W	...	K
Ivan	W	S	...	R
James	H	B	...	Q

Qualifications are added from a set list which is the same regardless of if the data is in "Qualification 1, 2 or N". These fields are only there as qaulifications are added as they are gained in time.

I need to return a count of "how many people have each qualification?", which in the above example for W is 3. (Ivan, Harry & Dave) or for A is 4 (Alice, Charles, Dave & Emily).

Data in each column is unique, ie Emily cannot have qualifications A,B,...,A. (or using this data example, not more than 26 qualifications if the pick list for quals is A to Z).

Help welcome please!

[Anonymous]

Just realised first line says data is in 2 fields: it is in my current project but I need to know how to expand for n fields for other projects too.

[Quy_Nguyen]

Hi,

Try crosstable function to transform it to:

Name	QualificationType	Value
Alice	Qualification 1	A
Alice	Qualification 2	B
Alice	Qualification N	C
Brian	Qualification 1	B
Brian	Qualification 2	C
Brian	Qualification N	X

Ex:

CrossTable(QualificationType, Value,1)
Load
Name,
Qualification1,
Qualification2,
..
QualificationN
From XXX;

Then expression to count how many people have qual A:  Count({<Value = {"A"}>}Name)