extract unique entries based on two criterias

[snjpverma]

I want the output as shown in Cell A13. All the unique names should be extracted (unique should be determined as per the Name and Employee ID because there are people with same name but different employee ID.

Asset tool.xlsx
	A	B	C	D	E
1	Sales peron	Emp ID	Profits earned	Dates	Store
2	Jack	15	₹ 20,957.00	03-04-2017	East
3	Bob	12	₹ 56,323.00	05-02-2018	West
4	Bob	12	₹ 31,892.00	09-07-2018	West
5	Jack	15	₹ 55,224.00	15-10-2018	East
6	Luke	30	₹ 27,937.00	29-08-2019	West
7	James	25	₹ 35,356.00	25-01-2022	East
8	Bob	12	₹ 29,117.00	30-05-2022	West
9	Ali	88	₹ 41,665.00	23-10-2022	East
10	Jack	15	₹ 29,451.00	02-11-2022	East
11
12
13	The following people have not met the requirement of minimum two submissions: 1) Luke 2) Ali and 3)James.
Sheet2

Thank You

 

[Peter_SSs]

just noticed that the system on which I am trying to work this out does have a TEXTJOIN function

Does that mean that you need to update your Account details?

 

[Peter_SSs]

unique should be determined as per the Name and Employee ID because there are people with same name but different employee ID.

Based on that I think you would need a modification to the TEXTJOIN suggestion.

21 08 12.xlsm
	A	B	C	D	E
1	Sales peron	Emp ID	Profits earned	Dates	Store
2	Jack	15	20957	4/03/2017	East
3	Bob	12	56323	2/05/2018	West
4	Luke	23	31892	7/09/2018	West
5	Jack	15	55224	10/15/2018	East
6	Luke	30	27937	8/29/2019	West
7	James	25	35356	1/25/2022	East
8	Bob	12	29117	5/30/2022	West
9	Ali	88	41665	10/23/2022	East
10	Jack	15	29451	11/02/2022	East
11
12
13	The following people have not met the requirement of minimum two submissions: Luke-23, Luke-30, James-25, Ali-88
Make list

Cell Formulas
Range	Formula
A13	A13	="The following people have not met the requirement of minimum two submissions: "&TEXTJOIN(", ",1,IF(COUNTIF(B2:B10,B2:B10)=1,A2:A10&"-"&B2:B10,""))

 

[snjpverma]

Thanks Peter, I certainly needed to look at the Employee ID as well because there are different people of same names.
Secondly, I will certainly take care of changing the Office Version in future if I happen to seek a solution for an excel other than 2016 again. Thank you for the reminder.

 

[Peter_SSs]

You're welcome.

 

[snjpverma]

Deleted