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 . 😀
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.
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
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 .
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?
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
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
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?
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.
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
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 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.
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.
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.
*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-* ruclips.net/video/tYvJ942YzQU/видео.html
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.
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.
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
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 . 😀
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.
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.
Finally, I found a way to remove duplicate rows with two matching conditions! Very much appreciated.
Well done i was trying to find a solution for long... Thank you very much!!!!
THANK YOU MY BROTHER FROM ANOTHER COUNTRY AND ANOTHER FAMILY!!!
damn bro, thanks so much... you saved me the hassle of writing a long python script
Perfect, straigt to the point, just what I was searching for!
What a fantastic video, this has saved me hours of work.
I've been looking for this for a long time. Thank you for such brilliant how-to!!!!
Great video, Ive used this nifty trick at work a few times and it saved me a lot of trouble!
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
You are awesome!! You have save me so much time and asking others for help!!!!
Very well explained , thank you for showing the formula in easy way thanks
Now that is a very handy video, learnt something new today and its so useful, thanks so much.
incredible, thank you, the main thing is how to rich to understand this logical check using excel. Worthy of appreciation
This is the best explanation ever. Thank you!!
Thanks!
Wonderful. You explain in a interesting way. Thank you very much dear
thank you so much!! this is something i wish i had learned a while ago!!
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.
👍
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
Thank you very much, I did like the video, greetings from Nicaragua!🙋♂️
Excellent presentation. I would have loved to see how you handle columns also.
Thankyou sir, you have solved my biggest problem
Fantastic! Very useful information. Well put together and very easy to understand.
Great & Very Useful.. Thanks🙏
You are a lifesaver!!! Thank you!!!!
Thanks a million. You are God's sent.
Thankyou very much!! Helped me 👍
Thank you! This video has saved me hours, possibly days!
This information is very helpful. Thank you for sharing.
Thanks for the new insight, really helpfull!
👍
Mind blowing...thank you so much
Thank you very much. I used this for my DFS lineups (daily fantasy football)
Thanks Man I That's Was Really Helpful I Was Looking For Something Exact For My Current Work
👍
Thank you very much, it really helps ^^
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 .
Best video I saw in the subject
this is great, thank you!
That's a great bunch of tricks ! Thanks !
Thanks!
What about if the data could be in different columns but I still want that to be the same duplicate?
is there a way to do this on google sheets?
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?
Create a new column 1 2 3 4 5 ..
Sort the data upside down using this column.
Then proceed as usual.
Bloody brilliant
Helped a lot!! Thankyou!
this was very good
Very informative, thank you!
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
ThankQ Man Don't stop doing this!!!
Amazing. Thanks.
Wowiee explanation
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
Thanks mate!
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?
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.
This was great! Thanks!
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
Thankyou so much☺️
:)
Well done bro!!
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.
Try to see if a Pivot Table works for you with ticket # in rows and count of ticket # in values.
Thank you
Thanks Sir
Is it possible not to have the UID column and the set the conditional formatting with a consolidated statement?
I'm afraid I don't really understand what you're trying to do.
@@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.
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.
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.
Very useful! Thanks.
Is there a way , once you have identified the duplicates to total the numbers in one column and delete the other rows?
Check out pivot tables.
thx you
Excellent...
good job bro
perfect!!!!!
How to do this with multiple sheets?
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.
Your channel looks great, thank you, subscribe to your channel.
*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-* ruclips.net/video/tYvJ942YzQU/видео.html
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.
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.
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