Compare and summarize two columns

[JARichard74]

Column A contains an alphanumeric project number e.g., AA23SLGA and there are 30 different projects in column A. Column B has a corresponding language i.e. the cell could say English or Français. Let's say A2:A50 are all for the same project AA23SLGA and K2:K50 have a corresponding language.

I would like to summarize the findings as follows: For each project # check the corresponding language so that if all of them are English then = English; if all are Français then = Français; if language is mixed i.e. some English and some Français then = Bilingual. After checking all the projects, I would get something like

AA23SLGA - English
AA23SLGB - English
AA23SLGR - Français
AA23SLGW - Bilingual
etc...

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E
1				Summary
2	AA23SLGA	English		AA23SLGA	English
3	AA23SLGA	English		AA23SLGB	English
4	AA23SLGR	Français		AA23SLGR	Francais
5	AA23SLGA	English		AA23SLGW	Bilingual
6	AA23SLGR	Français
7	AA23SLGB	English
8	AA23SLGW	English
9	AA23SLGR	Français
10	AA23SLGB	English
11	AA23SLGR	Français
12	AA23SLGW	Français
13	AA23SLGW	Français
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=SORT(UNIQUE(A2:A13))
E2:E5	E2	=IF(COUNTIFS($A$2:$A$13,D2,$B$2:$B$13,"English")=0,"Francais",IF(COUNTIFS($A$2:$A$13,D2,$B$2:$B$13,"Français")=0,"English","Bilingual"))
Dynamic array formulas.

 

[JARichard74]

Perfect! Thanks

 

[AhoyNC]

You're welcome. Thanks for the feedback.