Return all Unique Values Based on Criteria in a Single Cell

[austinandreikurt]

Is it possible to list all unique values based on criteria into a single cell with "; " as the separator using only formula and not VBA? For example I have below data from A1-B14:

401k Loan 1	OTHER DEDUCTIONS
401k Loan 1	OTHER DEDUCTIONS
401k Loan 2	OTHER DEDUCTIONS
OT @ 1.5	ALL PROJECTS
OT @ 2.0	ALL PROJECTS
401k Catch Up Pre-Tax	Employee 401k
401K Roth Posttax	Employee 401k
Acc Vol EE aftertax	OTHER DEDUCTIONS
BFit Wellness Incentive	IGNORE
Child Support	OTHER DEDUCTIONS
Critical EE aftertax	OTHER DEDUCTIONS
Child Support	OTHER DEDUCTIONS
DP Vision Imp Income	IGNORE
Group Legal	OTHER DEDUCTIONS

I needed to have the RESULT in Cell E1 to be the unique values under "OTHER DEDUCTIONS" in Column B as criteria and this should be the result:

401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support; Critical EE aftertax; Group Legal

 

[shift-del]

Hi

If you could update to Excel 365 it would simply be:

 =TEXTJOIN("; ",TRUE,UNIQUE(FILTER(A1:A14,B1:B14="OTHER DEDUCTIONS")))

 

[jtakw]

Hi,

As your profile shows you're on Excel 2016, and does not have TEXTJOIN as I, you can do this, then in F2, put =E15 and Hide column E.

Book3.xlsx
	A	B	C	D	E
1
2	401k Loan 1	OTHER DEDUCTIONS			401k Loan 1
3	401k Loan 1	OTHER DEDUCTIONS			401k Loan 1
4	401k Loan 2	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2
5	OT @ 1.5	ALL PROJECTS			401k Loan 1; 401k Loan 2
6	OT @ 2.0	ALL PROJECTS			401k Loan 1; 401k Loan 2
7	401k Catch Up Pre-Tax	Employee 401k			401k Loan 1; 401k Loan 2
8	401K Roth Posttax	Employee 401k			401k Loan 1; 401k Loan 2
9	Acc Vol EE aftertax	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax
10	BFit Wellness Incentive	IGNORE			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax
11	Child Support	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support
12	Critical EE aftertax	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support; Critical EE aftertax
13	Child Support	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support; Critical EE aftertax
14	DP Vision Imp Income	IGNORE			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support; Critical EE aftertax
15	Group Legal	OTHER DEDUCTIONS			401k Loan 1; 401k Loan 2; Acc Vol EE aftertax; Child Support; Critical EE aftertax; Group Legal
Sheet996

Cell Formulas
Range	Formula
E2:E15	E2	=IF(B2="Other Deductions",IF(COUNTIF(A$2:A2,A2)=1,E1&IF(E1="","","; ")&A2,E1),E1)