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

 

[jorismoerings]

Hi,

Try this:

Book1
	A	B	C	D	E	F	G
1	Sales peron	Emp ID	Profits earned	Dates	Store
2	Jack	15	20957	42828	East		Ali
3	Bob	12	56323	43136	West		James
4	Bob	12	31892	43290	West		Luke
5	Jack	15	55224	43388	East
6	Luke	30	27937	43706	West
7	James	25	35356	44586	East
8	Bob	12	29117	44711	West
9	Ali	88	41665	44857	East
10	Jack	15	29451	44867	East
Sheet2

Cell Formulas
Range	Formula
G2:G9	G2	=IFERROR(LOOKUP(2,1/((COUNTIF($G$1:G1,$A$2:$A$10)=0)*(COUNTIF($A$2:$A$10,$A$2:$A$10)=1)),$A$2:$A$10),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[snjpverma]

is it possible to get the result in one cell like in A13 from Post #1?

 

[jorismoerings]

Hi,

With your current Excel version being 2016 - No, not without having to go to a VBA solution.
More info - take a look at previous provided solutions here Substitution for TEXTJOIN

 

[snjpverma]

I am open to using VBA solution and it's been a long time since I wrote a VBA code.
Can someone please help on the same?

 

[jorismoerings]

I am open to using VBA solution and it's been a long time since I wrote a VBA code.
Can someone please help on the same?

did you look at the link? Shown is a complete VBA part which handles your question to concatenate everything into one cell.

 

[snjpverma]

thanks jorismoerings, I will surely make use of this ACONCAT. seems to be a very useful code. will post here in case of any help required with the Substitute function with the Aconcat.

 

[Eric W]

You can do it with some helper cells:

Book2
	A	B	C	D	E	F	G	H
1	Sales peron	Emp ID	Profits earned	Dates	Store
2	Jack	15	20957	42828	East		Ali	1) Ali
3	Bob	12	56323	43136	West		James	1) Ali 2) James
4	Bob	12	31892	43290	West		Luke	1) Ali 2) James 3) Luke
5	Jack	15	55224	43388	East			1) Ali 2) James 3) Luke
6	Luke	30	27937	43706	West			1) Ali 2) James 3) Luke
7	James	25	35356	44586	East			1) Ali 2) James 3) Luke
8	Bob	12	29117	44711	West			1) Ali 2) James 3) Luke
9	Ali	88	41665	44857	East			1) Ali 2) James 3) Luke
10	Jack	15	29451	44867	East			1) Ali 2) James 3) Luke
11
12
13	The following people have not met the requirement of minimum two submissions: 1) Ali 2) James 3) Luke
Sheet3

Cell Formulas
Range	Formula
G2:G9	G2	=IFERROR(LOOKUP(2,1/((COUNTIF($G$1:G1,$A$2:$A$10)=0)*(COUNTIF($A$2:$A$10,$A$2:$A$10)=1)),$A$2:$A$10),"")
H2:H10	H2	=IF(G2="",H1&"",H1&ROWS($H$2:$H2)&") "&G2&" ")
A13	A13	=IF(H10="","Everyone has met the requirement","The following people have not met the requirement of minimum two submissions: "&H10)

 

[snjpverma]

I just noticed that the system on which I am trying to work this out does have a TEXTJOIN function. Please let me know how can we simplify this with Textjoin?

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Sales peron	Emp ID	Profits earned	Dates	Store
2	Jack	15	20957	4/3/2017	East
3	Bob	12	56323	2/5/2018	West
4	Bob	12	31892	7/9/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/2/2022	East
11
12
13	The following people have not met the requirement of minimum two submissions: Luke, James, Ali
Sheet1

Cell Formulas
Range	Formula
A13	A13	="The following people have not met the requirement of minimum two submissions: "&TEXTJOIN(", ",1,IF(COUNTIF(A2:A10,A2:A10)=1,A2:A10,""))
Press CTRL+SHIFT+ENTER to enter array formulas.