Help creating a formula for this...

[Rachyst01]

Hello, I have a table that looks like this

I have to create a separate table to separate the names in the following way: 1 column for names with only new, one column for names with only existing and 1 column for names that have new and existing. I am struggling to find formulas for that and I am not sure how to arrange my data to make it easier. Note that many names have duplicates, which makes it harder.
Please, any idea would help

 

[Rachyst01]

Sorry, the formula for duplicates was wrong, refer to this screenshot instead

 

[lrobbo314]

Might be a better way, but this seems to work.

BookII.xlsm
	A	B	C	D	E	F	G	H
1	Name	New or Existing	Duplicates			Both	Existing Only	New Only
2	David	Existing	Yes			David	Peter	Axul
3	David	New	no			America	Elfie	Dorka
4	Axul	New	Yes				Rachel
5	Peter	Existing	Yes				Damon
6	Peter	Existing	Yes
7	Elfie	Existing	no
8	Rachel	Existing	Yes
9	Rachel	Existing	Yes
10	Dorka	New	no
11	Damon	Existing	no
12	America	New	Yes
13	America	Existing	Yes
Sheet6

Cell Formulas
Range	Formula
F2:F3	F2	=LET(r,A2:A13,ne,B2:B13,e,UNIQUE(FILTER(r,ne="Existing")),n,UNIQUE(FILTER(r,ne="New")),m,MATCH(e,n,0),ie,IFERROR(INDEX(n,m),FALSE),FILTER(ie,ie<>FALSE))
G2:G5	G2	=LET(r,A2:A13,b,F2#,f,NOT(ISNUMBER(MATCH(r,b,0))),nb,FILTER(r,f),m,ISNUMBER(MATCH(r,nb,0)),UNIQUE(FILTER(r,m*(B2:B13="Existing"))))
H2:H3	H2	=LET(r,A2:A13,b,F2#,f,NOT(ISNUMBER(MATCH(r,b,0))),nb,FILTER(r,f),m,ISNUMBER(MATCH(r,nb,0)),UNIQUE(FILTER(r,m*(B2:B13="New"))))
Dynamic array formulas.

 

[Eric W]

Another option:

Book1
	AD	AE	AF	AG	AH	AI	AJ
1
2
3	Name	New or Existing	Duplicates?		Only New	Only Existing	Both
4	David	Existing	Yes		Axul	Damon	America
5	David	New	Yes		Dorka	Elfie	David
6	Axul	New	No			Peter
7	Peter	Existing	Yes			Rachel
8	Peter	Existing	Yes
9	Elfie	Existing	No
10	Rachel	Existing	Yes
11	Rachel	Existing	Yes
12	Dorka	New	No
13	Damon	Existing	No
14	America	New	Yes
15	America	Existing	Yes
16
Sheet12

Cell Formulas
Range	Formula
AH4:AH5	AH4	=SORT(UNIQUE(FILTER(AD4:AD25,(COUNTIFS(AD4:AD25,AD4:AD25,AE4:AE25,"Existing")=0)*(AD4:AD25<>""))))
AI4:AI7	AI4	=SORT(UNIQUE(FILTER(AD4:AD25,(COUNTIFS(AD4:AD25,AD4:AD25,AE4:AE25,"New")=0)*(AD4:AD25<>""))))
AJ4:AJ5	AJ4	=SORT(UNIQUE(FILTER(AD4:AD25,(AD4:AD25<>"")*ISERROR(MATCH(AD4:AD25,AH4#,0))*ISERROR(MATCH(AD4:AD25,AI4#,0)))))
AF4:AF15	AF4	=IF(COUNTIF($AD$4:$AD$20,AD4)>1,"Yes","No")
Dynamic array formulas.

 

[Rachyst01]

Thank you, my final goal is to produce a count of those names but once I use COUNTA() in front of any of those formulas, the number I get is not accurate since when I sum up the total names without duplicates, it doesn't give the same number as when I add up the new, existing and both. Any idea on how to fix this?

 

[Eric W]

I'm a little confused. It sounds like you're trying to reconcile the 2 tables. You could just use a COUNTIF to see how many times each name appears:

Book1
	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1					98		93		110
2
3	Name	New or Existing	Duplicates?		Only New		Only Existing		Both
4	David	Existing	Yes		Axul	1	Damon	1	America	2
5	David	New	Yes		Dorka	1	Elfie	1	David	2
6	Axul	New	No				Peter	2
7	Peter	Existing	Yes				Rachel	2
8	Peter	Existing	Yes
9	Elfie	Existing	No
10	Rachel	Existing	Yes			2		6		4		12
11	Rachel	Existing	Yes									12
12	Dorka	New	No
13	Damon	Existing	No
14	America	New	Yes
15	America	Existing	Yes
16
Sheet12

Cell Formulas
Range	Formula
AH1,AL1,AJ1	AH1	=LEN(FORMULATEXT(AH4))
AH4:AH5	AH4	=SORT(UNIQUE(FILTER(AD4:AD25,(COUNTIFS(AD4:AD25,AD4:AD25,AE4:AE25,"Existing")=0)*(AD4:AD25<>""))))
AI4:AI5,AM4:AM5,AK4:AK7	AI4	=COUNTIF($AD$4:$AD$15,AH4#)
AJ4:AJ7	AJ4	=SORT(UNIQUE(FILTER(AD4:AD25,(COUNTIFS(AD4:AD25,AD4:AD25,AE4:AE25,"New")=0)*(AD4:AD25<>""))))
AL4:AL5	AL4	=SORT(UNIQUE(FILTER(AD4:AD25,(AD4:AD25<>"")*ISERROR(MATCH(AD4:AD25,AH4#,0))*ISERROR(MATCH(AD4:AD25,AJ4#,0)))))
AI10,AM10,AK10	AI10	=SUM(AI4:AI8)
AO10	AO10	=SUM(AI10:AM10)
AO11	AO11	=COUNTA(AD4:AD20)
AF4:AF15	AF4	=IF(COUNTIF($AD$4:$AD$20,AD4)>1,"Yes","No")
Dynamic array formulas.

If this isn't what you want, please manually create a screen shot of what you want the results to look like and show it to us.