Highlight and Count Duplicates in Row

Highlight and Count Duplicates in Row - Excel Tutorial

Рет қаралды 20,567

Күн бұрын

Пікірлер: 42

@akramalazrak327 2 жыл бұрын

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.

@Computergaga 2 жыл бұрын

You're welcome, Akram 👍

@drjosiahv Жыл бұрын

Thanks a lot. A very useful formula which I could not get even from Microsoft help

@Computergaga Жыл бұрын

You're welcome!

@AhmedSC Жыл бұрын

Simply YOU ARE A LEGEND 😮

@Computergaga Жыл бұрын

👍😊 Thank you

@nadermounir8228 2 жыл бұрын

Thank u Alan. this is a great video 📹 especially the old school solution. Thank u again :)

@Computergaga 2 жыл бұрын

You are very welcome Nader. Thank you for your comments.

@garethwoodall577 2 жыл бұрын

Great explanations Alan!

@Computergaga 2 жыл бұрын

Thank you Gareth.

@WasabiSoySauce Жыл бұрын

Very helpful! Thank you!

@Computergaga Жыл бұрын

You're welcome!

@koraron Жыл бұрын

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

@Computergaga Жыл бұрын

You are so welcome!

@leilas2119 Жыл бұрын

excellent thank you so much!

@Computergaga Жыл бұрын

You're very welcome!

@JCEurovisionFan1996 2 жыл бұрын

I subscribed to your channel, Alan. Thanks!

@Computergaga 2 жыл бұрын

Thanks for the sub! 😊

@mcclousky1197 Ай бұрын

Hi Alan. Great video. It has helped me a lot. I have been looking for something similar as well. I want to know if there are any duplicates between two different areas of a spreadsheet, where there are several columns in between that need to be omitted. Such as: =COUNT.IFS($G2:$L2&$AJ2:$BH2;G2)>1 The problem is, that I don't know which code to use to select distinct rows, and even if it is possible. "&" and "+" does not work. Can you tell me if this is possible?

@teoxengineer 2 жыл бұрын

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

@Computergaga 2 жыл бұрын

Thank you Emre. I have a link in the video description where I do break down that formula with F9 😊

@LiaqatAli-qk8yp Жыл бұрын

Wonderful

@Computergaga Жыл бұрын

Thank you very much!

@adilalsabahi7251 6 ай бұрын

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?

@Computergaga 6 ай бұрын

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.

@swashofc Жыл бұрын

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.

@swashofc Жыл бұрын

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

@jeffwoodcock6702 2 жыл бұрын

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).

@JackieChung-eo3il 2 жыл бұрын

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.

@Computergaga 2 жыл бұрын

You're welcome, Jackie. The formulas return 2 if you enter Gita three times.

@AliRaza-fd3xz 2 жыл бұрын

How to find duplicates in excel on iPad

@thtgrldiana6388 2 жыл бұрын

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?

@Computergaga 2 жыл бұрын

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.

@thtgrldiana6388 2 жыл бұрын

@@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. 🙂

@Computergaga 2 жыл бұрын

@@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.