THIS IS THE BEST!!! thank you i finally can tidy my datas love u! :) (i tried a lot function but somehow they didn't work until i finally saw this video)
Thank you so very much. Your clear explanation of this instructional video has saved me so much time. May the law of action and reaction benefit you in many ways for helping complete strangers save time.
This video has helped me many times with all the duplicates I find when I do SEO keyword research! Thank you so much 😄 By the way, add the 0:00 time stamp so the chapters activate, they aren't working now. I hope it helps hehe
Ребята, спасибо огромное за видео! Всё супер! Преподаватель прекрасна, английский как у носителя! Всё по делу! Смотрю не отрываясь! Побольше бы такого качественного контента в сети! Но хотел бы вставить свои 5 копеек, основанные на опыте. 🤔 Тот метод, который показан в видео не совсем верен. Он работает только в том случае если у КАЖДОГО клиента свои уникальные значения и даты в столбцах G, H и I. Для того, чтобы проверить это достаточно просто скопировать значения из ячеек G6, H6, I6 в ячейки G5, H5, I5. Как только Вы их скопируете, то Ваш алгоритм найдет, что они дублируются. Но вот только дублироваться они будут не у Truva, а просто будут встречаться 1 раз у Truva, а второй раз у Softwarely. Ваш алгоритм поставит TRUE на дублирование строки 6, но она будет совпадать по полям "Users", "Payment Amount" и "Transaction Date" не со своими строками 16 и 17, а с чужой - 5-ой строкой! Прошу прощения за буквоедство, но я почитал каменты и увидел, что люди пользуются этим алгоритмом на практике... А это чревато ошибками и багами. 😉 Человек без практического опыта сразу не разберется. Сам на работе (в Экселе) делал просто и без затей: сцеплял все интересующие значения (тупо и в виде текста), а потом искал дубликаты. Дёшево и сердито! Работает безотказно даже на нескольких десятках тысячах строк! 😂 Ну и файлы у вас немного другие приложены. Не хватает строки 17. В обоих. 😉
Hey. This worked almost perfectly. I was just wondering if there was a way to make it to where the formula doesn’t count the space. I notice when I leave a space at the end of some it doesn’t highlight the duplicates. Thank you very much
Thanks! GSheets is not that smart yet, and it might miss some duplicates as in your case + if you use lower/upper case in other formulas. I suggest to use Data -> Trim whitespace on your dataset so that all extra spaces are removed at once.
Hi there! Try something like this: 1.) In conditional formatting choose the column you want to do the highlight in and set it as a range (without the headers row), for example E2:E 2.) Write a custom formula like =E2=LARGE($E$2:$E, 2) which will find and highlight the second highest value in column E. Let us know if it works for you!
The formula does not seem to work when I am trying to highlight the duplicates from only 2 columns, it does work when I have more than 2 columns though. Can you help?
Hello, could you, please, refer to the exact type of duplicate highlight you are trying to use? Also, did you try using formulas from our cheat sheet? docs.google.com/spreadsheets/d/1t81-xpIXyzq7OGBtQH6QH-lejGy2wi5CTCj6IwK3v0o/edit#gid=1078846374
Hang on...I don't think this works exactly. Isn't it checking each column independently? Meaning that if there was a match in Col A and in Col G and in Col H and in Col I, any match at all for each, then that row will be flagged as a duplicate. But each of those comparisons is not necessarily flagging the same duplicate value, right? So in your example, let's add one more entry. Maybe we can copy down Row 15 "DevDevils" into Row 18, but then change the date (Col I) to "01-10-2018" (the same date as for Row 2 "SmartTalk"). Now in your multiple comparison example Row 2 will also be highlighted whereas previously it was not, because now each of its compared cells has a match somewhere else, even though the TRUE comparisons are coming from different rows. Is that right, and is there any way to fix that issue? I'm asking because I have a similar situation with a large dataset. Each column has multiple duplicates, but what I really want is to compare row to row. Thanks!
The logic of the formulas has a problem. It is looking to see if the cell has a duplicate in any of the other rows, not rows that match all the other columns. If you take a cell in one of the duplicate columns and change to another value in another row (not on the real duplicate), it will still think the column is a duplicate because the value in that one column is a duplicate
THIS IS THE BEST!!! thank you i finally can tidy my datas love u! :) (i tried a lot function but somehow they didn't work until i finally saw this video)
Thank you so very much. Your clear explanation of this instructional video has saved me so much time. May the law of action and reaction benefit you in many ways for helping complete strangers save time.
This video has helped me many times with all the duplicates I find when I do SEO keyword research! Thank you so much 😄
By the way, add the 0:00 time stamp so the chapters activate, they aren't working now. I hope it helps hehe
is there a way to highlight duplicates between different sheets? thanks
This was the best video I have ever seen. It literally helped me clean up a list with over 2600+ in a few seconds. This is really great.
So great to hear that! Thanks for your feedback!
Ребята, спасибо огромное за видео! Всё супер!
Преподаватель прекрасна, английский как у носителя! Всё по делу!
Смотрю не отрываясь! Побольше бы такого качественного контента в сети!
Но хотел бы вставить свои 5 копеек, основанные на опыте. 🤔
Тот метод, который показан в видео не совсем верен.
Он работает только в том случае если у КАЖДОГО клиента свои уникальные значения и даты в столбцах G, H и I. Для того, чтобы проверить это достаточно просто скопировать значения из ячеек G6, H6, I6 в ячейки G5, H5, I5.
Как только Вы их скопируете, то Ваш алгоритм найдет, что они дублируются. Но вот только дублироваться они будут не у Truva, а просто будут встречаться 1 раз у Truva, а второй раз у Softwarely.
Ваш алгоритм поставит TRUE на дублирование строки 6, но она будет совпадать по полям "Users", "Payment Amount" и "Transaction Date" не со своими строками 16 и 17, а с чужой - 5-ой строкой!
Прошу прощения за буквоедство, но я почитал каменты и увидел, что люди пользуются этим алгоритмом на практике... А это чревато ошибками и багами. 😉 Человек без практического опыта сразу не разберется.
Сам на работе (в Экселе) делал просто и без затей: сцеплял все интересующие значения (тупо и в виде текста), а потом искал дубликаты. Дёшево и сердито! Работает безотказно даже на нескольких десятках тысячах строк! 😂
Ну и файлы у вас немного другие приложены. Не хватает строки 17. В обоих. 😉
amazing same solution i was looking for , Great Video
Superb teaching style
Thanks ma'am. This is one user from India. Ma'am we request you to please upload some more basic lectures of Google sheets.🙏
Thanks for your feedback, Ayush. We do consider adding more Google Sheets tutorials. Stay tuned!
I want to be able to count the duplicates from multiple tabs is that possible?
can you do this if they are in diferent sheets ?
Instead of single column.. Is it not workable for whole sheet? And is it workable if there any merged cells?
How would I find reverse duplicates; for example, A1= Bob B1= Ann, A2= Ann B2= Bob? I would like to disallow this double entry.
can you please show what button you pressed for the programing to come down
Hello, do you mean these buttons showing/hiding the script? monosnap.com/file/EGOITFoIzMuJPOQoTAgTNRFSYSzWvw
Thank you very much for an explicit explanation!
You are welcome!
Hey. This worked almost perfectly. I was just wondering if there was a way to make it to where the formula doesn’t count the space. I notice when I leave a space at the end of some it doesn’t highlight the duplicates. Thank you very much
Thanks! GSheets is not that smart yet, and it might miss some duplicates as in your case + if you use lower/upper case in other formulas. I suggest to use Data -> Trim whitespace on your dataset so that all extra spaces are removed at once.
Hello mam, I learnt from this video is very useful. I would like to know how to identify duplicates from multiple sheets in google sheet
Using the same idea explained in this video I could separate 7000 duplicates from the telecalling list I had
Good work
please tell me highlight second highest number in column .formula in condition format
Hi there! Try something like this: 1.) In conditional formatting choose the column you want to do the highlight in and set it as a range (without the headers row), for example E2:E 2.) Write a custom formula like =E2=LARGE($E$2:$E, 2) which will find and highlight the second highest value in column E. Let us know if it works for you!
Hi this video is super. And pls explain about duplicate text vaule highlight in different colours.If is possible can u pls explain here
Could pls explain how to use vlookup in google sheet
Hey, those are some great techniques. Thanks and God bless you! 🙂🙏
I am not able to get the same results as u got for the transaction column
these videos are awesome
Didn't find the solutions i want that "how to highlights same number in two different spreadsheet", can you able to do this ???
Not sure that's possible, especially if we are speaking about two different files (not 2 tabs in the same file)
Thank you very much!
How I can highlight duplicate in two sheets
thank you
Thank you for this!
Thanks for the great piece of information. Also have request , please do a video about how to remove duplicates and basics of VLOOKUP function.
Thank you! VLOOKUP is on our list. We will get back to video production very soon. Please, follow our updates!
The formula does not seem to work when I am trying to highlight the duplicates from only 2 columns, it does work when I have more than 2 columns though. Can you help?
Hello, could you, please, refer to the exact type of duplicate highlight you are trying to use? Also, did you try using formulas from our cheat sheet? docs.google.com/spreadsheets/d/1t81-xpIXyzq7OGBtQH6QH-lejGy2wi5CTCj6IwK3v0o/edit#gid=1078846374
how to find duplicates in two different sheets based on multiple conditions? Please help me
Thank you! Dankeschön from germany :-)
Our pleasure!
Please tell me how to find duplicates in two different files and how to highlight them? I foun videos only for finding duplicate in same file.
Hi! Just import all of your data into the same file to compare. We have a tutorial on how you do so: ruclips.net/video/VCcQfpUEtgI/видео.html
Great !!!!!
Hang on...I don't think this works exactly. Isn't it checking each column independently? Meaning that if there was a match in Col A and in Col G and in Col H and in Col I, any match at all for each, then that row will be flagged as a duplicate. But each of those comparisons is not necessarily flagging the same duplicate value, right?
So in your example, let's add one more entry. Maybe we can copy down Row 15 "DevDevils" into Row 18, but then change the date (Col I) to "01-10-2018" (the same date as for Row 2 "SmartTalk"). Now in your multiple comparison example Row 2 will also be highlighted whereas previously it was not, because now each of its compared cells has a match somewhere else, even though the TRUE comparisons are coming from different rows. Is that right, and is there any way to fix that issue? I'm asking because I have a similar situation with a large dataset. Each column has multiple duplicates, but what I really want is to compare row to row. Thanks!
Oh, I think I got it. If you use COUNTIFS instead of COUNTIF, it should work! Thanks, this video definitely helped get me on the right track :D
Hi Sushant, great! We're happy that you got this one sorted out, and that the video was helpful :) Thanks for your feedback!
How can check duplicate data in same row ?
how can we show different color to different duplicates
Not sure that's possible to cover with a single custom formula, unfortunately
awesome
thank you!
The logic of the formulas has a problem. It is looking to see if the cell has a duplicate in any of the other rows, not rows that match all the other columns. If you take a cell in one of the duplicate columns and change to another value in another row (not on the real duplicate), it will still think the column is a duplicate because the value in that one column is a duplicate
Shit like this is why excel is superior
how to duplicate 2 row only?