Excel - Find Duplicate Rows Based on Multiple Columns

[@danielnjovu3652]

Finally I can now sleep at night, I am Data Associate for a USAID project here in Zambia and I have been having issues in my office a lot of duplicates coming every single day causing me having sleepless nights, thank you very much man, I can now pull duplicates and deal with them.

[@lalithasrivani6799]

Thank you so much sir. I checked all other videos to compare rows , but most of them are just comparing single column values , not entire rows . Concatenation and then conditional formatting is great idea . 😀

[@Kungu]

What can I say... this is the kind of trick you think it was obvious... once you have watched it. GENIOUS!!! WONDERFUL. I subscribed. Many thanks.

[@ahmadullahahmadzai8834]

THANK YOU MY BROTHER FROM ANOTHER COUNTRY AND ANOTHER FAMILY!!!

[@davidperry1285]

Finally, I found a way to remove duplicate rows with two matching conditions! Very much appreciated.

[@manolisdelimpaltadakis2370]

Well done i was trying to find a solution for long... Thank you very much!!!!

[@marcmanohar5500]

Very well explained , thank you for showing the formula in easy way thanks

[@thedude8153]

Great video, Ive used this nifty trick at work a few times and it saved me a lot of trouble!

[@t_balle1620]

damn bro, thanks so much... you saved me the hassle of writing a long python script

[@reisfuseini2445]

This is the best explanation ever. Thank you!!

[@ExcelGoogleSheets]

Thanks!

[@Haydee007]

You are awesome!! You have save me so much time and asking others for help!!!!

[@pfunf]

I've been looking for this for a long time. Thank you for such brilliant how-to!!!!

[@lukeg9667]

Thank you senpai!! Literally googled first half of the morning trying to find a solution, i get home and the first search gives me the answer lol thanks bro

[@saberrezaei7444]

incredible, thank you, the main thing is how to rich to understand this logical check using excel. Worthy of appreciation

[@kinganthonyx]

Wonderful. You explain in a interesting way. Thank you very much dear

[@Viper7513]

What a fantastic video, this has saved me hours of work.

[@PPanchal762]

Thankyou sir, you have solved my biggest problem

[@VITALSYSINDIA]

thank you very much..Just saved my day...Was looking for something similar to this and your video just made it simple and helped me complete my task.

[@ExcelGoogleSheets]

👍

[@benlaorg5807]

Perfect, straigt to the point, just what I was searching for!

[@itsquia1739]

You are a lifesaver!!! Thank you!!!!

[@turlock123]

Now that is a very handy video, learnt something new today and its so useful, thanks so much.

[@willykitheka7618]

Excellent presentation. I would have loved to see how you handle columns also.

[@babatundeatoyebi7725]

Thanks a million. You are God's sent.

[@veronicabrill2503]

thank you so much!! this is something i wish i had learned a while ago!!

[@navnathdugaje9135]

Great & Very Useful.. Thanks🙏

[@angelinegrace2746]

Thanks so much. This really helped. I wish i got the idea of seeing this long back. My work would have completed many days ago

[@boluadeoye5226]

Thank you so much for this fantastic and easy to understand video. However, could you pls explain how to remove duplicates by keeping the latest row and highlighting the precious one?

[@ExcelGoogleSheets]

Create a new column 1 2 3 4 5 .. Sort the data upside down using this column. Then proceed as usual.

[@monu228]

Thankyou very much!! Helped me 👍

[@daniele.solorzano9515]

Thank you very much, I did like the video, greetings from Nicaragua!🙋‍♂️

[@st.mikhael]

Thanks for the new insight, really helpfull!

[@ExcelGoogleSheets]

👍

[@tableaututorialstips71]

Mind blowing...thank you so much

[@g1max]

Thanks Man I That's Was Really Helpful I Was Looking For Something Exact For My Current Work 👍

[@RealEstateStiles]

This information is very helpful. Thank you for sharing.

[@EddyCharger]

is there a way to do this on google sheets?

[@TheAnjana777]

That's a great bunch of tricks ! Thanks !

[@akshaydm619]

Best video I saw in the subject

[@nidhirajeev1398]

Helped a lot!! Thankyou!

[@NotN2Limits]

Fantastic! Very useful information. Well put together and very easy to understand.

[@Vincent1263]

Thank you very much, it really helps ^^

[@hichamhadj9640]

Very informative, thank you!

[@FantasyCouch]

Thank you very much. I used this for my DFS lineups (daily fantasy football)

[@LG-pr8lr]

Thank you! This video has saved me hours, possibly days!

[@lakhilisoukaina9347]

Hi , Thanks for sharing , please i have a question how to do if I want to color just the duplicates in one column with the condition of having 2 different corresponding cells in the other column ? Thank you .

[@vinayreddypulagam]

ThankQ Man Don't stop doing this!!!

[@birasafabrice]

this is great, thank you!

[@rajeshr8887]

As always excellent explanation. I have a Google Spreadsheet sheet that auto populates my end of the day stock position along with Market Index value Mon to Fri, this is done by a trigger at a predetermined time. Works fine on all days with the exception of trading holidays. The trigger will be generated but the value will not have changed from the preceding day’s value so now I have a duplicate row. I have to manually delete such rows to make the data look neat, but have been searching for a solution that automatically delete this row. Any suggestions will be appreciated. Cheers

[@WalkerB2730]

Bloody brilliant

[@SaffireChantal_Music]

This was great! Thanks!

[@WilliamEllison]

this was very good

[@1sornram]

Thanks!

[@farooktabana]

Well done bro!!

[@lloydchristmas4547]

Amazing. Thanks.

[@gayathris3098]

Wowiee explanation

[@callumstevens2754]

What about if the data could be in different columns but I still want that to be the same duplicate?

[@raymundgelacio2307]

Very useful! Thanks.

[@sushantkatkar2356]

Thankyou so much☺️

[@ExcelGoogleSheets]

:)

[@ertf7777]

Thanks mate!

[@chandannasta]

suppose I have 1 in column A, and in column B I have multiple rows with each row showing different numbers 1, 3, 5 or 5,3,2 or 4,3,7 and so on so how do I find duplicate which row has number 1 in column b. Is there any way to remove 1 as duplicate in column B as it already shows in column A

[@TheKoreanWombat]

Is there a way to do this, but instead of highlighting the later ones highlight the earlier ones? In my spreadsheet I only want to keep the latest line and I can’t find a way to do it

[@dsa5820]

Hi, I am not sure if I will get a reply but I have an issue following these steps. After concatenating the first row, I am unable to just copy the formula for the next rows automatically. It is needing me to manually change the cell numbers from A2 t oA3, etc in order to work. Does anyone know how to troubleshoot this issue or why I am getting it? Thanks

[@thtgrldiana6388]

This is awesome, however I'm hoping to highlight and hide the duplicates, but I want to see the number of occurrences for each unique/duplicate. example: I have 1 row for ticket #'s 1, 3, 6, and 7 , but I have 2 rows for tickets #2 and #5, and I have 3 rows for ticket #4. I want to hide the dups, but see the count of dups in a single row for each ticket with duplicates, plus still see my unique values. This is a support-ticket scenario, how many times an entry was made to a to a single ticket.

[@ExcelGoogleSheets]

Try to see if a Pivot Table works for you with ticket # in rows and count of ticket # in values.

[@alokghosh9311]

Excellent...

[@tonydata1155]

Thank you

[@musatenginkeri1823]

Thanks Sir

[@paropamiz2440]

good job bro

[@jay.chow-wt]

Is it possible not to have the UID column and the set the conditional formatting with a consolidated statement?

[@ExcelGoogleSheets]

I'm afraid I don't really understand what you're trying to do.

[@jay.chow-wt]

​@@ExcelGoogleSheets What I meant was that you created a UID column (01:50) to concatenate the other 2+ columns so you can compare the combined contents as a single entity. I wonder if you can skip that interim step and find duplicates of A&B&C… directly. I tried, but I don't think you can create a data range that is the outcome of concatenating 2+ columns. Thanks for sharing your expertise.

[@ExcelGoogleSheets]

It's possible using new MAP, BYROW, LAMBDA functions, but quite complicated for most users to understand. I have videos covering these on the channel.

[@sadiqueakhter2010]

I have one million records in each column and a total of 5 columns are there in the same worksheet. How to remove duplicates from all the columns? i.e. If Column A has 1 and Column B and C also has 1, the record of 1 should get deleted from Column B and C.

[@bjohnson9067]

Is there a way , once you have identified the duplicates to total the numbers in one column and delete the other rows?

[@ExcelGoogleSheets]

Check out pivot tables.

[@opie1kanobie]

Why in your first example, does it remove TWO duplicates...there is only ONE duplicate. The other row it removed did NOT have "a, blank, b,c" in rows 6 AND 7 prior to the running "remove duplicates." It only had row 6 as a match???? Not clear on why that removed row 7 that was NOT a duplicate.

[@g.theodoridou4888]

perfect!!!!!

[@blythecarlos4746]

How to do this with multiple sheets?

[@angelique4787]

thx you

[@microsoftoffice8257]

Your channel looks great, thank you, subscribe to your channel.

[@SharifulIslam-fp4yk]

*If your data are arranged among multiple columns in Microsoft Excel and you want to find the cell or data values are common among all these columns using VBA within moment of just a click then you can check-* kzbin.info/www/bejne/qorZe2xqZ76th7c

[@arindamoitra58]

Your channel has some fantastic videos but this is a very disappointing video. Quite a long video and too many unnecessary steps to solve multiple column duplicates issue when one single formula can be used in custom conditional formatting to find and highlight duplicates. Use =COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)>1 in conditional formatting to highlight duplicate rows.

[@ExcelGoogleSheets]

Usually I wouldn't comment on this type of feedback, but I have to make sure this channel stays with ACCURATE information. The formula you have, I've seen people use, but it's NOT accurate and you shouldn't be using it. This comes from people not understanding how COUNTIFS function works when condition is a blank cell. Try your formula with 3 columns A - blank cell - B and then next row again A - blank cell - B and see what you get. So I stand by my video.

[@tylermassey3554]

Is there ANY way to do this without creating an extraneous column? Why can't the same formula that searches for duplicates also do the concatenation beforehand?

[@tylermassey3554]

After only finding explanations like this one, I thought about it for a few minutes. I believe this formula will work for Google Sheets conditional formatting D,E,F,G, and H are the columns that will be concatenated and searched =COUNTIF(ARRAYFORMULA(D:D&E:E&F:F&G:G&H:H),D1&E1&F1&G1&H1)>1 I'm sure this can be improved with some well placed absolute references ('$') but its working for my purposes.