Check if Unique ID has 2 values in a range

[Oredo]

Hello Everyone,

I have survey data arround 400k rows and i would like, where Column A has user ID (unique) and Column B has 2 answer options : 1. Fundamental , 2. Advanced

I would like to find out:

How many Users given only fundamental answers and how many there was who answered fundamental and advanced.

For example User A has 3 rows of data and all his answers were "Fundamental" only in all 3, where User B had 4 rows of data 2 rows as "Fundamental" and other 2 with "Advanced"

i want in column C to say "Only Basic" for user A and "Both" for user B .

Data is sorted by survey send date, so User A can appear in row 20 or row 200,000 etc.

Thank you guys

 

[alz]

How about this approach?

Excel Message boardgfgf.xlsm
	A	B	C	D	E	F
1	User ID	Answer		User ID	Fundamental	Advanced
2	A	Fundamental		A	3	0
3	B	Advanced		B	2	2
4	A	Fundamental		C	0	1
5	A	Fundamental		D	1	0
6	B	Fundamental
7	B	Fundamental
8	C	Advanced
9	B	Advanced
10	D	Fundamental
11
25

Cell Formulas
Range	Formula
E2:F5	E2	=COUNTIFS($B:$B,E$1,$A:$A,$D2)

 

[Oredo]

How about this approach?

Excel Message boardgfgf.xlsm
	A	B	C	D	E	F
1	User ID	Answer		User ID	Fundamental	Advanced
2	A	Fundamental		A	3	0
3	B	Advanced		B	2	2
4	A	Fundamental		C	0	1
5	A	Fundamental		D	1	0
6	B	Fundamental
7	B	Fundamental
8	C	Advanced
9	B	Advanced
10	D	Fundamental
11
25

Cell Formulas
Range	Formula
E2:F5	E2	=COUNTIFS($B:$B,E$1,$A:$A,$D2)

Much obliged Alz,

I was reaching too far in arrays, where CountIfs works just fine.
You have a great day sir