=IF(COUNTIFS(A7:A11,A7:A11,B7:B11,B7:B11)>1, "Duplicate", "") for Excel 365 and 2021. Power Query definitely is my favorite if updates will be expected. Set up once and use again and again. I have learned a lot from your videos. Thank you and wait for your next video.
@MyOnlineTrainingHub Жыл бұрын
Great to hear 🙏
@tmb8807 Жыл бұрын
One of the things I like about Excel is that there are multiple ways to accomplish essentially the same task. Within the scope of one workbook personally I've been using the dynamic array functions (UNIQUE in this case) more and more. Power Query is also great (and I guess might be less resource-intensive with bigger data sets?) but I really like everything just updating real-time, without having to refresh or re-do anything.
@MyOnlineTrainingHub Жыл бұрын
Good points for when to use the different options 🙏
@tanjasahaidak16173 ай бұрын
You are GENIUOS! In comparison to other tutorials (despite of topic), you are like superhero! Thanks for all, what you show us for free.
@MyOnlineTrainingHub3 ай бұрын
Thank you for your kind words! You're very welcome. 😊
@IvanCortinas_ES Жыл бұрын
All known. Good material, quite complete. Thank you very much Minda.
@MyOnlineTrainingHub Жыл бұрын
Cheers, Ivan!
@trinitys.78334 ай бұрын
this video is really helpful, THX a lot. And I want to share another simple way to highlight and remove duplicates by using WPS Office. 1. select the range of your values 2. click “highlight duplicates”< “set” or if you want to remove duplicates 1. select the range of your values 2. click “highlight duplicates”< “remove duplicates” very simple and quick.
@chrisg758 Жыл бұрын
Even with seemingly simple topics, you manage to point out features and capabilities I did not know. Thank you!
@MyOnlineTrainingHub Жыл бұрын
Glad it was worth your time to watch, Chris!
@christopherthompson4283 Жыл бұрын
The duplicate rows example you did in the highlight duplicates with conditional formatting section is tricky. The only reason they were both highlighted is because the values appeared multiple times on the respective lists. If you did a series of 1,2,3,4,5 and the column beside 2,3,4,5,6 and did that step all of the numbers 2,3,4,5 on each list would be highlighted (1 and 6 would not be). That specific function looks at the entire selection of cells for anything that appears more than once, NOT the contents of an entire row with the contents of other rows. I'm not sure if that is how you originally intended to explain that part, but the video you showed made it look like it was looking for entire duplicated rows. Which you explain how to do in the next section haha.
@MyOnlineTrainingHub Жыл бұрын
Yes, good point 👍
@bccabernet9 ай бұрын
another brilliant video, Mynda! Thank you so much. You make these videos easy to understand -even if some of us aren't as advanced as others (or need to be ie: retired). I love learning from you and always share your videos.
@MyOnlineTrainingHub9 ай бұрын
Thanks so much for your kind words and support 🥰
@alexanderbaranov5418 Жыл бұрын
Thanks, Mynda, for the valuable overview! Kisses from Ukraine
@MyOnlineTrainingHub Жыл бұрын
My pleasure, Alexander! Thinking of you over there.
@mcwahaab Жыл бұрын
Very useful! I regularly use Unique and conditional formatting, but got quite other tips today
@MyOnlineTrainingHub Жыл бұрын
Great to hear!
@missamo80 Жыл бұрын
Loving the new backdrop to go along with the great tips!
@MyOnlineTrainingHub Жыл бұрын
Thanks so much!
@Seftehandle Жыл бұрын
Very useful. Love how you implemented PQ as well.
@MyOnlineTrainingHub Жыл бұрын
Thanks so much!
@zahoorsarbandi2982 Жыл бұрын
you always come with useful videos which are helpful in daily work. Thanks
@MyOnlineTrainingHub Жыл бұрын
Glad to hear that 🙏
@chrism9037 Жыл бұрын
Thanks Mynda, this was great!
@MyOnlineTrainingHub Жыл бұрын
Thanks so much, Chris!
@marjoriefrancis4560 Жыл бұрын
Thank you so much for your tutorials they helped me with my capstone project. Awesome job!!
@MyOnlineTrainingHub Жыл бұрын
Great to hear!
@chrisklimantiris2520 Жыл бұрын
Thank you for sharing Knowledge!! Appreciate it.!!
@MyOnlineTrainingHub Жыл бұрын
You're welcome 😊
@ivanbork4175 Жыл бұрын
Hi Mynda All good examples, and as you point out it depend on the use, which one to choose. In my job I prefer to use the power editor, and since most of the data I’m working with contains dates, I will need to sort them before removing duplicates. Absolutely not to criticize the always good videos you make, but maybe you can cover the “table.buffer” function, at least it took me some time to figure out how to keep the one that I want to use.
@MyOnlineTrainingHub Жыл бұрын
Thanks, Ivan! Yes, specifying which duplicate to keep with Power Query is tricky. A video for another day, perhaps 😊
@YJWest2 ай бұрын
Hi Mynda! This is yet another informative video that is really helpful for me. You may already have a video on this, but I was hoping to better understand the 'Replace Duplicates' function for a table. For example, if I have .csv files saved every week. Is the best way to incorporate new data and remove duplicates by using Power Query? I know you can remove duplicates from the data once you've copied/pasted the new data into the table? That's what I'm doing right now, manually...baby steps. I have a dataset that increases by at least 5,000 rows each month but I want to make sure I get rid of duplicate data by ensuring that the most recent data is in the table. I currently copy/paste the new data into Excel and then sort by the number col. (a-z) and then by updated col. (z-a) and then I remove duplicates from the table. In other words I want to ensure that I have the latest updated data (i.e. something that was new last month could be closed this month). I'm sure there is an easier way. Thanks for your help.
@MyOnlineTrainingHub2 ай бұрын
Yes, absolutely use Power Query for this. You won't know yourself when all you need to do is click a button to update the data. You can learn Power Query in my course here (and if you get stuck implementing the techniques in your own work as a course member you can email me and I'll help you): www.myonlinetraininghub.com/excel-power-query-course
@leksdic Жыл бұрын
With countif there's also a way to find not only the duplicates but also which of the 2 was the second occurrence, in case this matters. Countif is quite powerful when used correctly.
@MyOnlineTrainingHub Жыл бұрын
Yes, great alternative to the =IF(COUNTIF(... example in the video to just use =COUNTIF(...
@samahmedalone6573 Жыл бұрын
Thanks 👍
@MyOnlineTrainingHub Жыл бұрын
Pleasure 😊
@nadermounir8228 Жыл бұрын
Nice video Thank u for ur hard work
@MyOnlineTrainingHub Жыл бұрын
Thank you 😊
@stevenstern11559 ай бұрын
Thank you for the video. Could you create a video on how to remove blanks using text from a row?
@MyOnlineTrainingHub9 ай бұрын
Not sure what you mean. If there are blanks, why do you need to use text from a row? Note: I won't see your response here due to the volume of comments. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@eng.ahmedwaznah2261 Жыл бұрын
Really Useful Many thanks.
@MyOnlineTrainingHub Жыл бұрын
Glad to hear that 😊
@JulioCesarContrerashabilsa Жыл бұрын
Excelentes opciones, útiles y bien explicadas y graficadas. graciass.!!
@MyOnlineTrainingHub Жыл бұрын
Gracias!
@rnunez047 Жыл бұрын
Gracias... guardaré el video
@MyOnlineTrainingHub Жыл бұрын
Thank you!
@kylelane630 Жыл бұрын
Thank you!
@MyOnlineTrainingHub Жыл бұрын
You're welcome!
@samsquamsh78 Жыл бұрын
Thanks for yet another great video! I have string values in a column (let’s call it column1) which contains string values in LARGE CAPS and in Mixed caps. I want to split it into two new columns with one holding all string values in LARGE CAPS and the other holding the Mixed caps. How do I do this? It seems like an easy problem but I have tried everything I can think of and asked chatGPT and it does not come up with anything that works. Please help!!
@MyOnlineTrainingHub Жыл бұрын
Thank you 🙏You can probably use List.ContainsAny to look for the capitalised alphabet and extract that text into a separate column. The remaining words can go in another column. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@viktorasgolubevas2386 Жыл бұрын
Not sure - quick idea =FILTER(column1, EXACT(column1, UPPER(column1))) =FILTER(column1, NOT(EXACT(column1, UPPER(column1))))
@petraliverani1641 Жыл бұрын
EDIT: I've just seen a much easier way =EXACT(A2,UPPER(A2)) will return TRUE for all upper and you can work out a suitable way of splitting from there. I have just been working with a long IFS formula to do something quite different but I believe it is the same principle. No doubt Mynda would know an easier way but it works! When you say large and mixed caps do you mean All Caps and Mixed Case? If you so you can set up a long IFS(ISNUMBER(FIND formula that looks for each lower case letter of the alphabet (FIND is case specific). Let's say your values are in Col A. Mixed Case formula In Col B you have your =IFS(ISNUMBER(FIND formula to find Mixed Case =IFS(ISNUMBER(FIND("a",A2)),A2,ISNUMBER(FIND("b",A2)),A2,ISNUMBER(FIND("c",A2)),A2, ... etc All Caps will return #N/A which you can avoid with ISERROR-type element if you wish All Caps formula In Col C you put a formula to say if Col B value #N/A, Col A value for All Caps. For my long IFS formula I created it by separating all the elements like so: Col 1: ISNUMBER(FIND(" Col 2: a Col 3: ",A2)),A2, And then copied down Cols 1 and 3 for all the values I needed to search on adding initial "=IFS(" and putting an ending parenthesis instead of comma, changing the letter of the alphabet in Col 2 in each row and then concatenating all the bits and making text of the formula before pasting it in as a formula. Hope this helps if Mynda doesn't help you with something easier.
@petraliverani1641 Жыл бұрын
@@viktorasgolubevas2386 Alternatively, the formula =EXACT(A2,UPPER(A2)) will return TRUE for all upper case and you can work it out from there.
@viktorasgolubevas2386 Жыл бұрын
@@petraliverani1641 Sure. Second argument of FILTER does the same :) but in a more versatile "dynamic" manner for the range/array named "column1": row-by-row, cell-by-cell...
@sachin.tandon Жыл бұрын
Nice work. This might be helpful. I used to get very odd behaviour using COUNTIF embedded in a lambda function that was called from within another LAMBDA (something to do with it not accepting the variable which although was a vector, was strictly not a RANGE, which COUNTIF must take as its first argument). It did my head in! This formula avoids using COUNTIF, and IMO is more robust (when doing complex and nested lambdas) . Hope its useful COLUMN_DUPLICATES = LAMBDA(col_vector, LET( count, SCAN(0, col_vector, LAMBDA(a, r, SUM(--(r = col_vector)))), duplicates, IF( IFERROR(ROWS(UNIQUE(FILTER(col_vector, count > 1))), 0) = 0, "No Duplicates", UNIQUE(FILTER(col_vector, count > 1)) ), duplicates ) );
@MyOnlineTrainingHub Жыл бұрын
Thanks for sharing, @sachin.tandon!
@txreal211 ай бұрын
How to use formulas if data is a table (instead of range)? I need the table format to also do other data stuff. Learning from you :)
@MyOnlineTrainingHub11 ай бұрын
It's difficult to visualise what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@JoseAntonioMorato Жыл бұрын
Dear Mynda, In the "Exctracting with Formulas" worksheet, the extraction is of the unique values, but you can extract, by formula, the duplicate value(s): =UNIQUE(VSTACK(UNIQUE(A7:B11),UNIQUE(A7:B11,,1)),,1) - (duplicate line) OR =UNIQUE(VSTACK(UNIQUE(B7:B11),UNIQUE(B7:B11,,1)),,1) - (duplicate cell) 🤗
@MyOnlineTrainingHub Жыл бұрын
Good ideas. Or with FILTER: =FILTER(A7:B11,COUNTIFS(A7:A11,A7:A11,B7:B11,B7:B11)>1) - duplicates =FILTER(A7:B11,COUNTIFS(A7:A11,A7:A11,B7:B11,B7:B11)=1) - unique/distinct
@JoseAntonioMorato Жыл бұрын
@@MyOnlineTrainingHub Dear Mynda, Your formula for displaying duplicates with the FILTER function is better, because it shows the amount of repetitions. ❤🤗
@ofeaghomatse342411 ай бұрын
Great👍
@MyOnlineTrainingHub11 ай бұрын
Thank you!
@user-el2ls5bp4j9 ай бұрын
Hi Mynda - lovely presentation, as usual! I am trying to link to the hi-res version of the PDF, but it only loops me back to the article. Can you update the link? Thanks!
@MyOnlineTrainingHub9 ай бұрын
Thanks so much! The cheat sheet PDF is available to download from the article page just above the video. See the heading on that page "Download Example Workbook & Cheat Sheet". If you have any problems downloading it, please reach out via email: website at MyOnlineTrainingHub.com because I won't see follow up replies to this thread due to the volume of comments.
@steven.h0629 Жыл бұрын
Seven.. yikes! 👍😎✊
@MyOnlineTrainingHub Жыл бұрын
😁
@teoxengineer Жыл бұрын
Mynda hi, thank you. After determining duplicate values then I want to filter those values with their own different cell ccolors How can I filter with two different colors at the same time because excel filter button allows me to select just one color?
@MyOnlineTrainingHub Жыл бұрын
You'd have to find another way. Either select multiple items in the Text filters or write a conditional formatting rule with a formula that applies the same colour to all cells you want to filter using the OR function.
@muhanadmawasalkazmeh137 Жыл бұрын
Can we use pivot tables to show duplicates in two columns or more
@MyOnlineTrainingHub Жыл бұрын
Yes, just add the other column to the row labels.
@TheJoshtheboss Жыл бұрын
Is there a way to highlight duplicates between 2 workbooks? With identical sheets layout, where values might differ.
@MyOnlineTrainingHub Жыл бұрын
I'd use Power Query to do this, as explained here: www.myonlinetraininghub.com/excel-compare-two-lists
@TheJoshtheboss Жыл бұрын
@@MyOnlineTrainingHub Thanks.
@gurkiclk Жыл бұрын
👍
@MyOnlineTrainingHub Жыл бұрын
Cheers!
@RossMaynardProcessExcellence Жыл бұрын
Great video. I've been struggling with the problem of duplicates today and we haven't solved it, so any ideas would be welcome. I have a large data table from which I need to remove duplicates based on one column (essentially a case ref). However, I want to choose which of the duplicates to remove based on the entry in another column To simplify, let's say I have two rows with the same case reference but in the second column one row is "A" and one is "B". I want to keep the Bs but the As and Bs can appear in any order. Rows with the same case ref can have several As and Bs. I want to keep only one of the rows with a "B" Make sense? Thoughts?
@MyOnlineTrainingHub Жыл бұрын
Yes, you can do this, but it's difficult to explain here. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@RossMaynardProcessExcellence Жыл бұрын
@@MyOnlineTrainingHub Thank you. I will post the file Monday as not working today. I did have an idea in my sleep - to custom sort the table by case ref with secondary sort by the second column Z to A. That would bring the Bs to the top of each case. I then remove duplicates leaving those cases that do have Bs with that row. Would that work? If there is more than one B on a case I don't think it matters which I leave and which I remove. If that solution works then it shows the power of the subconscious!
@MyOnlineTrainingHub Жыл бұрын
That will work for some of the methods, but it won't work for Power Query.
@RossMaynardProcessExcellence Жыл бұрын
@@MyOnlineTrainingHub That did work for my particular problem so thank you for helping my brain cells work it through.
@JJ_TheGreat Жыл бұрын
4:45 Doesn't the UNIQUE() function remove duplicates - because it would only list a duplicated value once!
@MyOnlineTrainingHub Жыл бұрын
Isn't that what I said?
@osamaessam Жыл бұрын
If I have a big set of numbers in a matrix form, with no headers (examples: 10 rows, and 10 columns, hence 100 numbers). How can I remove duplicates from this set of data?
@MyOnlineTrainingHub Жыл бұрын
You're best to use the UNIQUE function to extract a list of distinct values. If you want them converted into a column of values, you can use the TOCOL function e.g. =TOCOL(UNIQUE(...))
@vishalwadekar88049 ай бұрын
What if you have double amount in another coloum like coloum A1 having 50 and column B having 100?
@MyOnlineTrainingHub9 ай бұрын
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@vishalwadekar88049 ай бұрын
Just for example if you have number 10,20,30,40,50 in column A and have double number in coloum B like 20,42,36,80,102 and you have to separate them or show them they are duplicate
@pc-doctor1416 Жыл бұрын
If I could learn & remember just 10% of what you know about Excel I'd be happy.
@MyOnlineTrainingHub Жыл бұрын
😊 keep practicing and you'll get there.
@moesadr334210 ай бұрын
What if, we only have one column and need to highlight those with the first 5 matching characters on the same column?
@MyOnlineTrainingHub10 ай бұрын
Add a column with this formula: =LEFT(cell containing the value, 5) then find duplicates on the new column.
@moesadr334210 ай бұрын
@@MyOnlineTrainingHub Much appreciate the prompt response. Thanks
@moesadr334210 ай бұрын
Much appreciated! @@MyOnlineTrainingHub
@electrovoltmce5 ай бұрын
how do I disable the option to detect duplicate values in excel - how do I disable this option - I want to have duplicate values????
@MyOnlineTrainingHub5 ай бұрын
Excel doesn't tell you there are duplicates unless you ask it to, so there's nothing to disable.
@electrovoltmce5 ай бұрын
Wrong - I spent 2 hours until I understood where to activate duplicates - to disable the option to find duplicates - Home - Conditional Formatting - Manage Rules - Select Rules - Delete Rules.@@MyOnlineTrainingHub
@MyOnlineTrainingHub5 ай бұрын
My bad. I thought you were asking me how to disable something that is on by default. It wasn't clear that you had set up a Conditional Formatting rule and simply wanted to delete it. That's as easy as Home tab > Conditional Formatting > Clear Rules.
@electrovoltmce5 ай бұрын
you are right, but it is too late.@@MyOnlineTrainingHub
@aprilmorales38248 ай бұрын
Super desperate!! I have 3 data that are the same and I need to remove 2 so leaves one unique value.
@MyOnlineTrainingHub8 ай бұрын
Hope you found a solution. If you're having trouble, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum