Alan so great explanation about counting of dublicate values with different aproaches. However, I think it will be better to use F9 key in order to evaluate formula result in Formula because I haven’t understood sumproduct 1/counta function. If you are able to demostrate formula evaluation by using F9 key, we can get the results which are coming from formula. 🙋🏼♂️ I am going to try getting last result myself in excel ☺️ Good topic thank you again
Thanks Alan very clear explanation, I just subscribed to your channel. One more question please, how about if want to count the duplicate in the whole sheet? lets assume, we have 3 companies and I want to know the employees registered in more than one company using their Identity Number?
You're welcome! Thanks. You could use the COUNTIFS function for this. If the ID number was in column A, you could use =COUNTIFS($A$2:$A$50,A2). This will count their occurrences. This is a brief example that can be adapted to needs. I have a video on this channel somewhere.
Thank you for the video, I think this is the exact solution I am searching for. However, at 3:49, it does not allow me to select the first cell of the row. I have tried both clicking and typing the name or just typing the formula and pasting it in the conditional formatting rule, but I am getting the error box that there is something wrong with the formula. Here is what I tried for a row with 10 columns: =COUNTIFS($O2:$X2,O2)>1 To reiterate, it does not allow me to select the O2 cell without getting an error, and typing the formula out results in an error, too. Any idea what could be causing the issue? I don't see how what I have tried is different than in the video.
In case someone is as silly as I am, this error had to do with my version of Excel accepting semi-colon, not comma for the separation of the criteria inside the parentheses. So the following worked perfectly: =COUNTIFS($O2:$X2;O2)>1
Hi Alan! This was great! thank you! My data is in rows with all the data points in columns Trans#; name, Amount, Item, Action. if a person buys something, then returns it, it is the same trans#. I would like to count my duplicate Trans #'s , and hide the duplicates for Trans#'s hide them in such a way that: only one row for the Trans # is visible and I can see there are X-number of duplicates but those rows are simply not visible. (sort of like treating the dublicated values as secondary rows). Is this possible?
You could use a COUNTIFS function in a new column to count the occurrences of the Trans#. For example, the formula =COUNTIFS($A$2:$A2,A2) works on the idea that the trans# is in range A2 down. You can then filter by the column to exclude the rows that contain a 1.
@@Computergaga Thank you and true... I also need to see those unique trans# where only one action is executed. I'm trying Pivot tables options.. but still not getting quite what I'm hoping for. 🙂
@@thtgrldiana6388 sure. You can use PivotTables and filter by the values in the column with COUNTIFS. Filter for 1 or 2. If you're on 365, you can also use the FILTER function.
I find this feature of excel is more flexible in my work and it helps me to do job properly.
Thanks from deep of my heart.
You're welcome, Akram 👍
Thanks a lot. A very useful formula which I could not get even from Microsoft help
You're welcome!
Simply YOU ARE A LEGEND 😮
👍😊 Thank you
Alan so great explanation about counting of dublicate values with different aproaches.
However, I think it will be better to use F9 key in order to evaluate formula result in Formula because I haven’t understood sumproduct 1/counta function. If you are able to demostrate formula evaluation by using F9 key, we can get the results which are coming from formula. 🙋🏼♂️
I am going to try getting last result myself in excel ☺️
Good topic thank you again
Thank you Emre. I have a link in the video description where I do break down that formula with F9 😊
Thank u Alan. this is a great video 📹 especially the old school solution. Thank u again :)
You are very welcome Nader. Thank you for your comments.
Great explanations Alan!
Thank you Gareth.
Very helpful! Thank you!
You're welcome!
I love u... Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, ... and a million more times. Dude i can thx u enough... life saver video xD
You are so welcome!
On the "No of Dups" col, I reckon that the following form, should also work: =COUNTA(UNIQUE(B3:E2, TRUE) - COUNTA(UNIQUE(B3:E2, TRUE, TRUE).
Wonderful
Thank you very much!
Thanks Alan very clear explanation, I just subscribed to your channel. One more question please, how about if want to count the duplicate in the whole sheet? lets assume, we have 3 companies and I want to know the employees registered in more than one company using their Identity Number?
You're welcome! Thanks.
You could use the COUNTIFS function for this. If the ID number was in column A, you could use =COUNTIFS($A$2:$A$50,A2).
This will count their occurrences.
This is a brief example that can be adapted to needs. I have a video on this channel somewhere.
excellent thank you so much!
You're very welcome!
I subscribed to your channel, Alan. Thanks!
Thanks for the sub! 😊
Dear Alan;
If one wants to list only the repeated ones:
=UNIQUE(HSTACK(UNIQUE(B2:E2,1,0),UNIQUE(B2:E2,1,1)),1,1) 🤗
How to find duplicates in excel on iPad
Thank you for the video, I think this is the exact solution I am searching for. However, at 3:49, it does not allow me to select the first cell of the row. I have tried both clicking and typing the name or just typing the formula and pasting it in the conditional formatting rule, but I am getting the error box that there is something wrong with the formula. Here is what I tried for a row with 10 columns:
=COUNTIFS($O2:$X2,O2)>1
To reiterate, it does not allow me to select the O2 cell without getting an error, and typing the formula out results in an error, too. Any idea what could be causing the issue? I don't see how what I have tried is different than in the video.
In case someone is as silly as I am, this error had to do with my version of Excel accepting semi-colon, not comma for the separation of the criteria inside the parentheses. So the following worked perfectly:
=COUNTIFS($O2:$X2;O2)>1
Thank you Alan. My question is if in Wk3 there were 3 Gitas and 1 Adam, then the No of Dups will be 2 (not 1)...how could I fix it? Thanks.
You're welcome, Jackie. The formulas return 2 if you enter Gita three times.
it trully working
Wohoo!! 🙂
Hi Alan! This was great! thank you! My data is in rows with all the data points in columns Trans#; name, Amount, Item, Action. if a person buys something, then returns it, it is the same trans#. I would like to count my duplicate Trans #'s , and hide the duplicates for Trans#'s hide them in such a way that: only one row for the Trans # is visible and I can see there are X-number of duplicates but those rows are simply not visible. (sort of like treating the dublicated values as secondary rows). Is this possible?
You could use a COUNTIFS function in a new column to count the occurrences of the Trans#. For example, the formula =COUNTIFS($A$2:$A2,A2) works on the idea that the trans# is in range A2 down.
You can then filter by the column to exclude the rows that contain a 1.
@@Computergaga Thank you and true... I also need to see those unique trans# where only one action is executed. I'm trying Pivot tables options.. but still not getting quite what I'm hoping for. 🙂
@@thtgrldiana6388 sure. You can use PivotTables and filter by the values in the column with COUNTIFS. Filter for 1 or 2.
If you're on 365, you can also use the FILTER function.
Hi , when I write to B2-C2-D2-E2 "Sally" formula result is 3. Is this normal ? or should be 1 ?
Hi Ezel, I don't understand what you're doing. You can't use the formula B2-C2-D2-E2 on text values.
@@Computergaga sorry , problem iş my english :). I will try explain later.
No worries.