Determine Duplicate & Unique Based on List of Data

[muhammad susanto]

hi all

how to figure out this problem, see this my layout

fruits.xls
	D	E	F
1	List of Fruit		entry name (given)
2	Lemon		Banana
3	Apricot		Mango
4	Avocado		Grape
5	Banana
6	Blueberry
7	Mango
8	Coconut
9	Grape
10
11	my target/expected result		should be (with manually)
12	total duplicate name of fruit (have entry)	?	3
13	total unique name of fruit (not yet entry)	?	5
14	total percentage duplicate name of fruit	%?	37,50
15	total percentage unique name of fruit	%?	62,50
Sheet1

Cell Formulas
Range	Formula
F14:F15	F14	=(F12/8)*100

my target / expected result from cell E12 till E15
i'm using excel 2021/365.
anyone help me, big appreciated..
sst.

 

[shinigamilight]

F12: =SUMPRODUCT(--(COUNTIFS(D2:D9,F2:F4)>0))
F13: =SUMPRODUCT(--(COUNTIFS(F2:F4,D2:D9)=0))

 

[awoohaw]

How about this?
It uses offset function whichis volatile so if your spreadsheet is big it will slow it down.
Also, you will need to update your formulas from time to time.

Book2
	A	B	C
1	List of Fruit		entry name (given)
2	Lemon		Banana
3	Apricot		Mango
4	Avocado		Grape
5	Banana
6	Blueberry
7	Mango
8	Coconut
9	Grape
10
11	my target/expected result
12	total duplicate name of fruit (have entry)	?	3
13	total unique name of fruit (not yet entry)	?	5
14	total percentage duplicate name of fruit	%?	37.50%
15	total percentage unique name of fruit	%?	62.50%
Sheet2

Cell Formulas
Range	Formula
C12	C12	=COUNTA(OFFSET($C$2,0,0,COUNTA(C2:C9),1))
C13	C13	=SUM(--(MMULT(--(TRANSPOSE(OFFSET($C$2,0,0,COUNTA(C2:C9),1))=$A$2:$A$9),SEQUENCE($C$12,1,1,0))=0))
C14:C15	C14	=C12/COUNTA($A$2:$A$9)

 

[muhammad susanto]

hi awoohaw, thank you very much!! worked well.

 

[awoohaw]

hi awoohaw, thank you very much!! worked well.

you are welcome But @shinigamilight 's solution is more efficient and easier to understand.
SUMPRODUCT is also volatile, so I'm not sure which slows systems down more.

 

[muhammad susanto]

F12: =SUMPRODUCT(--(COUNTIFS(D2:D9,F2:F4)>0))
F13: =SUMPRODUCT(--(COUNTIFS(F2:F4,D2:D9)=0))

hi shinigamilight, it's work too...thank you.

 

[Fluff]

SUMPRODUCT is also volatile

No it isn't you can which functions are volatile here Volatile Excel Functions -Decision Models

 

[awoohaw]

No it isn't you can which functions are volatile here Volatile Excel Functions -Decision Models

Thanks @Fluff , this is agreat link and resource. I know there was a SUM"SOMETHING" that is/was volatile at one time. Maybe it was the SUMIF, but the documentation on that only mentions excel from 20 yrs ago.