Count Unique or Duplicate Values in a List

Рет қаралды 329,798

12 жыл бұрын

Have you ever had a friend yelled random numbers at you while you're counting values? Aside from wanting to toss your friend in a lake you'd like to have an easier way to count these numbers to tell which are unique and which are duplicates. If the list is small that not a probably to do this type of analysis. If the list is big then forget trying to do it in your mind. Why not just use Excel? It's basically a sophisticated counting application. There's actually a function in Excel called COUNTIF and it counts stuff that meets a criteria. Check our the video below to see how's it all done.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Пікірлер: 158

@DouglasBorses 10 жыл бұрын

Just what I was looking for extremely clear and concise -Thank you !!

@lillieajackson9309 6 жыл бұрын

I watched 5 videos before yours and was still like - "what?!?". Watched your video, spreadsheet calculated in minutes. Total life saver, thank you!

@DougHExcel 6 жыл бұрын

Hi Lilliea Jackson, glad you liked it, thanks for commenting!

@lamazz03 5 жыл бұрын

This video was FANTASTIC! It is the only one I've seen that stepped you through the logic of the formulas for the counting of unique/duplicate entries. Now that I understand how the formula works, I can use in so many applications! Just knowing the formula doesn't allow you to tweak it for different circumstances. This was fantastic!!! As an additional bonus, he saved me many hours of time with his keyboard short cuts and editing multiple formulas at once! Not to mention the double clicking to expand the formula down the column. This video is a true value! Thank you for taking the time to post it!

@DougHExcel 5 жыл бұрын

Hi Lari Anne Mazzitello, glad you liked it, thanks for commenting!

@mlsauron 10 жыл бұрын

Thank's, you've saved lot's of my long working days and nights!

@cpatricio1 10 жыл бұрын

thank you so much dough! this is very valuable and saved me a lot of time. more power to you and god bless!

@mdhanunjayareddy1029 9 жыл бұрын

Awesome, this pivot process helped me address my challenge of finding out no. of duplicates in a column. Thanks a lot. DJ

@joselicop872 8 жыл бұрын

Thank you Doug! I needed this. :)

@heyitsscubasteve 2 жыл бұрын

Thanks a lot, Doug. Your demonstration helped a lot with what I was trying to achieve in my spreadsheet!

@DougHExcel 2 жыл бұрын

Glad it was helpful!

@donpierce4176 10 жыл бұрын

Doug this is AMAZING for two weeks I was trying to know how to convert a text "number" to a real Number for further calculations; because the numbers I was geting was from a mid(Formula) which looked like a number but was a text. This I could not find anywhere. Your explanation are clear to the point and simple,Thank You Doug . If you got training things on excel especially formulars utilising IF function I am happy to pay for it...

@astonduncan1263 2 жыл бұрын

I am so happy i came across your page !

@DougHExcel 2 жыл бұрын

Glad you liked!

@meowphillic 9 жыл бұрын

Thank you. Your videos are just amazing

@gowdablr 8 жыл бұрын

Thanks for the tip. Very helpful.

@jayantiviswanath9785 5 жыл бұрын

Great video man..! Exactly the solution I needed. Thank you !

@DougHExcel 5 жыл бұрын

Hi Jayanti Viswanath, glad you liked it, thanks for commenting!

@PatriciaGellon 10 ай бұрын

Great solution! So well explained. Thank you!

@DougHExcel 10 ай бұрын

Hi Patricia Gellon, you're welcome!

@tkdburnout 10 жыл бұрын

GREAT GREAT GREAT VIDEO, you got a new subsriber ^^ I was looking for a while to find the good formula and you made it happen. :)

@jaog4462 2 жыл бұрын

Terrific explanation. You´ve just won a new subscriber 👍

@DougHExcel 2 жыл бұрын

Thanks for the sub!!

@calebbrooks5055 11 жыл бұрын

Extremely Helpful

@EduardoPiston 9 жыл бұрын

Would you know to teach me how to count duplicates and triples for 5 number drawn lotteries?

@elektralightepikk 8 жыл бұрын

Thank you Doug, this helped me a lot!

@DougHExcel 3 жыл бұрын

Thanks Mel Joseph, glad it helped!

@allanpark6046 4 жыл бұрын

Thank you, simple and intuitive

@DougHExcel 4 жыл бұрын

Thanks Allan Park!

@ColinMacIver 3 жыл бұрын

Many thanks for explaining this clearly, very helpful.

@DougHExcel 3 жыл бұрын

Glad it was helpful!

@venkatvns2741 5 жыл бұрын

Thank you Doug sir... I needed this. :)

@DougHExcel 5 жыл бұрын

You're welcome!

@preethiganesan8789 7 жыл бұрын

thank you so much.. the mentioned tutorial was useful for me.

@DougHExcel 7 жыл бұрын

Glad you found it useful!

@user-vt8qj9no5e 7 жыл бұрын

Thanks! Very usefull well explain guide!

@DougHExcel 7 жыл бұрын

Thanks for the comment!

@hemseamify 6 жыл бұрын

Thank you very much, That is exactly what I need. Very appreciate.

@DougHExcel 6 жыл бұрын

Hi hemseam...you're welcome, glad you liked!

@danielromero432 11 жыл бұрын

Congrats, that was very helpfull tanks a lot

@luislizarraga2379 10 жыл бұрын

Thank you, It was very helpfull

@earlymorningproductions9355 3 жыл бұрын

VERY helpful. It took me a few views (that's how I roll) but eventually it sunk in! My heartfelt thanks!

@DougHExcel 3 жыл бұрын

Fantastic!

@NenadKralj 7 жыл бұрын

That is exactly what I needed! I have repeating #text in #excel #cell Additionally - How to put a #sum into also? Like a 3 #formulas in one; all the above + sum?

@DougHExcel 3 жыл бұрын

Hi Nenad Kralj, thanks for the comment!

@dianab4067 4 жыл бұрын

Thank you

@DougHExcel 4 жыл бұрын

Glad it helped!

@royzvarghese4707 4 жыл бұрын

Its Superb.....Thanks very much

@DougHExcel 4 жыл бұрын

Hi Royz Varghese, thanks for the comment!

@andrewtaylor8755 Жыл бұрын

Great Job Sir!!!

@DougHExcel Жыл бұрын

Thanks!

@Dxb-ex3eq 6 жыл бұрын

THANK YOU FOR THIS VIDEO.

@DougHExcel 6 жыл бұрын

You're Welcome!

@danielosorio217 6 жыл бұрын

Doug H, thanks very much for posting this tips. A quick question: @ 5:07 you said to use the 'out key' press the = sign as a keyboard short cut to bring the SUM function. What is the 'out key' that we need to press along with the = sign to bring the SUM function? Thank you again.

@DougHExcel 6 жыл бұрын

That would be the ALT key, so type press on the ALT and "=" keys

@lamazz03 5 жыл бұрын

He actually said the "ALT Key" not "out key". Press the ALT Key and "=" key together and it autosums as a keyboard shortcut! BRILLIANT!

@Anandraj-is1yd 4 жыл бұрын

Thank you very much sir..............

@DougHExcel 4 жыл бұрын

Most welcome

@ap5293 6 жыл бұрын

actually i have been asked this question in interview and i didnt know the ans thanks to you now i know.

@DougHExcel 6 жыл бұрын

Hi ashish parmar, thanks for the comment!

@WikarsoFahri 4 жыл бұрын

Thanks for your information. This is solution.

@DougHExcel 4 жыл бұрын

Hi Wikarso Fahri...you're welcome, glad you liked!

@mohau987 5 жыл бұрын

Thanks for this helped a lot

@DougHExcel 5 жыл бұрын

Thanks Mohau Kulima, glad it helped!

@Caro.travelandart 5 жыл бұрын

Hi! thanks for the video. Could you please help me with the following concern? I have a list of series numbers, and I want to know how many duplicates are, but by using this formula, I would always be missing "the original" duplicate. What I mean is that in your excel, I would like for the "5" of cell A2 to be highlighted as well, since it is part of the duplicates. Is there any way to do that?

@DougHExcel 5 жыл бұрын

maybe some of the other videos can give some insight kzfaq.infosearch?query=count+duplicate

@DARIUSHAZIMI 6 жыл бұрын

Thank you Doug.

@DougHExcel 6 жыл бұрын

You're Welcome!

@tariqshahuk 9 жыл бұрын

hi Thanks for sharing, just want to know how to count total occurrence of 5, 8 , or 2 in the table. thanking you in advance.

@mikehdz9606 5 жыл бұрын

I have data running from B7 to R60, in which I need only a count of numbers only, but the fields contain duplicate numbers and text as well... I need to count only one of the duplicates and no text... So I ended up with =SUMPRODUCT((B7:R60"")/COUNTIF(B7:R60,B7:R60&"")) , but this is counting text as well, what criteria should I use for the formula not to count text and only numbers?

@DougHExcel 5 жыл бұрын

you might want to use the ISTEXT OR ISNUMBER function with this kzfaq.info/get/bejne/pcCefJODyK21ip8.html

@shaikhomar4633 5 жыл бұрын

Fantastic!!!

@DougHExcel 5 жыл бұрын

Thanks for the comment!

@noeldoyle2477 5 жыл бұрын

Doug I am finding your videos really useful and they are saving me time and giving me some real advantages. I have a question about counting occurrences of date stamps In a sheet column Their are a 1000 rows, Column A is time stamp which varies some thing like 20/05/2019 08:10:55 If I wanted to count how many times a certain date occurs say 22/05/2019 (time being ignored) how would I do that.

@DougHExcel 5 жыл бұрын

Might want to use the TEXT function to change it to text and then perform the lookup kzfaq.info/get/bejne/rZd1hdNeyqjdpac.html

@wintapang9013 10 жыл бұрын

thanks a lot !!!!!!!!!!!

@excelengineer9143 3 жыл бұрын

very smart , thank u

@DougHExcel 3 жыл бұрын

You're welcome!

@chellohello4105 5 жыл бұрын

i love you sir i find it... thanks vrymuch sir..

@DougHExcel 5 жыл бұрын

Hi Marchelcello Studios, glad you liked it, thanks for commenting!

@MrBassRenegade 9 жыл бұрын

I'm looking to count duplicate values that are consecutive. For instance: 4 4 5 2 2 2 2 4 2 2 2 2 6 6 8 2 7 I want to count the number of times 2 repeats four times. I feel like it's similar to what you're doing in this video, but not exactly the same

@kurt2rsenjazz 7 жыл бұрын

Thank you Sir

@DougHExcel 7 жыл бұрын

You're Welcome!

@lucaplacidichannel 4 жыл бұрын

Life saver!!!!!

@DougHExcel 4 жыл бұрын

Thanks Luca Placidi, glad it helped!

@rishiksarkar62 5 жыл бұрын

THANKS A LOT

@DougHExcel 5 жыл бұрын

You're Welcome!

@Atiks_Hassan_Studios 4 жыл бұрын

thanks .. extreme level

@DougHExcel 4 жыл бұрын

Hi Dhaka Bangladesh, thanks for the comment!

@shanegune1545 5 жыл бұрын

Thanks alot for the sharing. I just successful in placing the formula. Which was my real goal. Here is in text format if someone difficult to go through video. =COUNTIF($B$1:B1,B1) B is column name and 1 is the cell number.

@DougHExcel 5 жыл бұрын

Hi Shane Gune, thanks for adding to the thread!

@gloriaestikurniati6410 3 жыл бұрын

Thank you, you save my life

@DougHExcel 3 жыл бұрын

Thanks Gloria Esti Kurniati, glad it helped!

@ranjith392 7 жыл бұрын

Thank you very much...:-)

@DougHExcel 7 жыл бұрын

You're welcome!

@AmanVerma-lo2mq 6 жыл бұрын

Hi, i have some data in the Excel sheet and there is also a duplicates but i want count of the same data after removing the same.

@DougHExcel 6 жыл бұрын

maybe this vid will give an alternative solution for you kzfaq.info/get/bejne/lZaAfMSEyMiwc40.html

@Draconarious 4 жыл бұрын

What if I want Column E to automatically list all unique values in Column A as they are added and removed, and for Column F to to take the Unique Value designated to its left and count the number of instances where that value appears at any point in Column A?

@DougHExcel 4 жыл бұрын

Power Query can probably do that kzfaq.infosearch?query=power+query+unique

@preethiganesan8789 7 жыл бұрын

Hi Dough, ur videos are Great. Do you have any videos for segregating the unique and duplicate values? I mean the duplicate values have to be placed in a separate column and unique values separately in a column. Example: Names Unique Duplicates Sam Sam Sam Oliver Oliver Sara Sam Sara Sara Ben Ben Sam

@DougHExcel 7 жыл бұрын

Sounds like a potential video! :-) This would be done with a couple of steps. This video might give some insight kzfaq.info/get/bejne/ibt-mdyotb3NZqM.html

@figmauser1996 4 жыл бұрын

Thank you.

@DougHExcel 4 жыл бұрын

You're welcome!

@arpitpatel14591 6 жыл бұрын

Thanks, Can you please guide us to do the same thing in MS Access?

@DougHExcel 6 жыл бұрын

You're Welcome! Sorry don't know Access a well...:-(

@arpitpatel14591 6 жыл бұрын

No issues Sir.. :-)

@shadowhaven012 6 жыл бұрын

great, so how can i apply this count to a filtered column?

@DougHExcel 3 жыл бұрын

quickest is the copy only the unfiltered range and paste to new range or if you have O365 subscription the combo of the New Unique function

@gopi-ep6mb 4 жыл бұрын

Instead can we use highlight duplicates in home tab ?

@DougHExcel 4 жыл бұрын

Conditional Formatting is another option kzfaq.info/get/bejne/ncWgq5WVrLmZhXk.html kzfaq.info/get/bejne/o8ufmtJyxLO8nZs.html

@luthfi4164 2 жыл бұрын

for the unique part, instead of using if statement and sum, we can use count if again to count how many times number 1 show up right? minimize the way to count unique values 😁

@Robert8455 5 жыл бұрын

Thank you

@DougHExcel 5 жыл бұрын

You're Welcome!

@shubhampatel8491 6 жыл бұрын

this is awesome

@DougHExcel 6 жыл бұрын

Hi Shubham Patel, glad you liked it, thanks for commenting!

@shubhampatel8491 6 жыл бұрын

you deserve this

@hanalei412002 9 жыл бұрын

Hi, I am wondering if you know how to exclude strikethrough letters in a countif formula? At this time I am using =COUNTIF(E11:E37,"*B*") but it is still picking up my strikethrough letters which is giving me an in accurate value. Do you know how to exclude any type of strickthrough letters? thanks

@hanalei412002 9 жыл бұрын

***** Thanks for that, I have just reg. with them I'm waiting my approval so I can ask the question. Theres on post that talks a bit about it but not really what I am looking for. he called it =countnostrikethrough(E11:E37,"*A*") but when I tried it, this didn't work for me I maybe missing something. thanks again. www.mrexcel.com/forum/excel-questions/851864-excel-formula-excluding-strikethrough.html Excel formula for excluding strikethrough

@hanalei412002 9 жыл бұрын

***** Kool will do thanks for the link and all the help.

@hanalei412002 9 жыл бұрын

***** Ok here it is, I did what you said and they got back to me with this function: nonstrikethroughs. thanks for your help maybe you might want to do a video on this because it's no where to be found on forms or on youtube. Function StrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter Then StrikeThroughs = StrikeThroughs - Cell.Characters(X, 1).Font.Strikethrough End If Next Next End FunctionFunction NonStrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter Then NonStrikeThroughs = NonStrikeThroughs - (Not Cell.Characters(X, 1).Font.Strikethrough) End If Next Next End FunctionFunction CellsWithStringStrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter And Cell.Characters(X, 1).Font.Strikethrough Then CellsWithStringStrikeThroughs = CellsWithStringStrikeThroughs + 1 Exit For End If Next Next End Function

@kellynrivero497 5 жыл бұрын

How do I do these formulas with a filtered list?

@DougHExcel 3 жыл бұрын

Hi Kellyn Rivero, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!

@amitkumar060189 7 жыл бұрын

Is it possible to get result in a cell with single formula without helping column...........Like i want to count duplicate value from range then sum of the total duplicate with using Count-if and sum formula.

@DougHExcel 7 жыл бұрын

Maybe something like this would give some insights kzfaq.info/get/bejne/o698adWlr6-5kmg.html kzfaq.info/get/bejne/qbChoNR8p7Hbd6c.html

@amitkumar060189 7 жыл бұрын

No, I am looking to get result through formula only in a single cell without helping column and without pivot table. Like : Both condition should met under a formula, 1st it should count duplicate and then add all duplicate. Like : =IF(COUNTIF($A$2:$A2,A2)>1,1,"") this formula will count duplicate value from range.....and now how to sum formula under above to add all duplicate value.

@peetm7 9 жыл бұрын

Is it possible to put it into one formula - make one counter in one cell, just set the range? I can't add a column for the if(countif) formulas and I can't write a macro.

@archipelago9376 9 жыл бұрын

***** This doesn't work in Excel 2013

@peetm7 9 жыл бұрын

archipelago93 It does. I had to learn array functions (CTRL+SHIFT+ENTER) first, but the principle works in Excel 2013.

@peetm7 9 жыл бұрын

***** Thanx, took me some time, I learn array formulas, adapted the principle for my needs, my formula barely fits to my screen, but works nicely. Thanx mate.

@cescastar3371 5 жыл бұрын

Thank you for this. i had repeated number for each year...

@DougHExcel 5 жыл бұрын

You’re welcome

@Bullishtrader1182 5 жыл бұрын

If I would like to add same instances than how we can sum all the same instances. Please reply

@DougHExcel 5 жыл бұрын

try the sumif or sumifs functions kzfaq.info/get/bejne/h95-mbCb2M7af58.html

@Bullishtrader1182 5 жыл бұрын

@@DougHExcel thank you dear.

@rizkisenja4902 3 жыл бұрын

thank you 😭

@DougHExcel 3 жыл бұрын

You’re welcome!

@multicreations3640 4 жыл бұрын

Sir I have a question If I write a name in A row I want that name on D row how it's possible?

@DougHExcel 4 жыл бұрын

in row D, use =A1 (or whatever row number the value is in)

@21crazz 8 жыл бұрын

How do you combine the two? Say Blank = 0, Unique = 2 and Duplicates = 3.

@21crazz 8 жыл бұрын

***** Thanks for your reply. Sorry I was't being very descriptive but I got it figured out. Thanks to your video :)

@BalvirSingh-qs9kz 4 жыл бұрын

Sir, how unique subtotal ?

@DougHExcel 4 жыл бұрын

Sorry, don’t understand the comment/ question...

@mirrrvelll5164 3 жыл бұрын

I still didn't get it why is necessary to absolute on first $A$2:A ?,,,,, when I did without, I see no difference. I mean, what is purpose of it? It selects my range only...

@DougHExcel 3 жыл бұрын

if it were A2:A2, dragging the fill handled to copy down would give A3:A3, A4:A4, etc.

@daashkadaashka248 8 жыл бұрын

Hi Dough, i have five columns and so many rows data.first column is date/20160320,20160320,20160321 etc/, second is name/sarah, george etc/,third is sums. How can i remove dublicate and count it? EX data: 20160320 Sarah 2$ shokolad 20160320 Sarah 1$ sweet I need this: Date Name Sum Count 20160320 Sarah 3 1

@DougHExcel 3 жыл бұрын

Hi Daashka Daashka, sorry 🙁....but try a post on the mrexcel.com forum!

@bammbamm12 8 жыл бұрын

I have 20,000 names on a list, most of which are listed more than once. Can this little exercise really help me count how many times each name is entered?

@bammbamm12 8 жыл бұрын

***** Roger - thank you.

@q-relax7737 8 ай бұрын

Thank you. Related to above, can you please help with this. Please see below data in column A, The desired result are in column B, how can we get the results in column B with formula. Column A required output in Column B Cell A1= Peter Cell B1= Cell A2= John Cell B2= Cell A3= Peter Cell B3= 1 Cell A4= David Cell B4= Cell A5= Peter Cell B5=2 Cell A6= Blank cell Cell B6= Cell A7= Peter Cell B7= Cell A8= John Cell B8= Cell A9= John Cell B9=1 Cell A10= John Cell B10=2 Cell A11= Maria Cell B11= Cell A12= Blank cell Cell B12= Cell A13= Maria Cell B13= Cell A14= Maria Cell B14=1 Cell A15= Peter Cell B15= Cell A16= David Cell B16= Cell A17= Maria Cell B17=2 I have used =SUBSTITUTE((COUNTIF($A$1:A1,A1)-1),0,"") but it works fine only untill first blank cell in column A. In B7 this formula is giving 3, but it should be blank. Basically after every blank in Column A a new set of data develops. The requirement is to serial the duplicates between two blanks in column. Hope this explains the problem. Thank you. Much appreciated.