Hello,
My data is as follows:
Column A
1. Apple
2. Banana
3. Apple
4. Orange
5. Apple
6. Banana
7. Chickoo.
I want a formula which gives result as 2 as only 2 items apple & banana is duplicated irrespective of their number of occurances.
Thank you.
Vivek.
=sum(if(frequency(match(a1:a7,a1:a7,),match(a1:a7,a1:a7,))>1,1)). Cse
=SUM(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1)>1,1))
and enter as an array - using Control+shift+enter
ignore blanks - hence range can be more than the data you have
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or with Ctrl+Shift+Enter
=SUM(IFERROR((COUNTIF(A1:A99,A1:A99)>1)/COUNTIF(A1:A99,A1:A99),))
Thanks to all.
Additionally, I also want to find duplicate values i.e. apple & banana in 2 cells one below the other.
Last edited by Vivek2705; 11-29-2018 at 02:58 AM.
can you give an example
I want a formula in cell B1 to give apple & when I copy it down to B2 it should give banana as only these two are repeated items.
Try in B1 fill down until you get blanks.Formula:
Please Login or Register to view this content.
Dave
try in b2
=IFERROR(INDEX(A2:A15, MATCH(0, COUNTIF(B1:$B$1, A2:A15)+IF(COUNTIF(A2:A15, A2:A15)>1, 0, 1), 0)), "")
USE control + shift + enter
Solved..thanks a lot..
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks