Hi John.. thanks.. great summary of the ways to remove duplicate values. Just for fun, came up with my own way to do it with formulas: Helper column E, cell E3, formula: =IF(COUNTIFS($B$3:B3,B3,$C$3:C3,C3,$D$3:D3,D3)=1,ROW(E3)-ROW($E$2)) - copy down to E12 to create a helper column index of the row numbers within the table with the unique records. First extract column G, cell G3, formula: =IFERROR(INDEX(B$3:B$12,AGGREGATE(15,6,$E$3:$E$12,ROWS(G$3:G3))),"-") - copy down to row 12 and right to column I - extracts the columns and rows of data by matching the helper index number of the table to the data in the columns of the table. AGGREGATE screens out the error rows (those with FALSE). IFERROR puts a "-" if beyond the range of matches.. ready for new data, up to the point of copy/down. The result simulates what you achieved with the FILTER function. I couldn't figure out how to get the row index directly into a resultant array to use within AGGREGATE, so the helper column is necessary. But, not a bad way to go if you want something dynamic and formula driven on the worksheet.. and you don't yet have FILTER. Thanks for the inspiration to tinker and create. Thumbs up!!
I wanna remove the duplicates, but when we remove the serial no. changes and the blanks are gone. I just wanna let those rows blanks from where the duplicates are removed. Please provide me a solution.
can you help to trim and remove duplicate and text join using vba?. there are chances that the range I pull has duplicate value but does not get removed due to spaces in the end.so it should be trimmed and remove the duplicate.
Use power query to trim spaces first then remove duplicates. I see VBA as a last resort. These days there are much better tools available for most things.
Super video. Question- I have data in 6 column and data are same in all columns (data like- B200089NT67) when I select data range and apply condition formatting duplicate then all duplicate highlights but when go to duplicate remove option and apply then I find error "No duplicate find value" so how to solve it. I had also checked all data extra space using Trim and also checked cell value is correct. Example- Column1 Colum2 Column 3 DR000257DT TR56884QR ASG489901 DR000257DT
In formulas, instead of using the explicit reference to cells, I prefer to use the structured reference in the table. You used to fix cell E3, in the formula of the column «Count», the syntax =COUNTIFS($E$3:E3,[@Combined]), when the syntax of the table references could be used: =COUNTIFS(INDEX([Combined],1):[@Combined],[@Combined]). I consider this type of reference more appropriate, but it is personal preference. 😁 No offense notice, please. Hugs. 🤗
I noticed that the advanced filter results were different between the two methods. Can you please help me understand why/how filter in place and filter in a new location produced different results for the unique values?
I am having an issue with using VBA to try to remove duplicates from a specific column of data. Any time I define the column as a named range and try to delete the duplicates within the range, it keeps deleting duplicates outside the range. Here is the code: With ActiveSheet Dim rng As Range Set rng = Range("Receipt") rng.RemoveDuplicates Columns:=Array(1), Header=:xlNo End With The "Receipt" named group is Column D on my worksheet. The macro deletes duplicates from Column A, where I do not want to have duplicate information deleted. How can I ensure that the macro is not looking at data outside the specific range?
Is there anyway to enable the 'remove duplicate' option while the excel is in protected mode.... that option is getting disable when we keeps the excel sheet as protected
how can you do the other way around? Having only what you have at the end and going back and not manually, but automated of course. And I'm not talking about removing duplicates, but just removing the values from cells that are duplicates.
If I have excel 2013, How do I remove duplicate value that is the same in one column but in different rows. Smith 2013 9:00AM Smith 2013 9:03AM If I try to remove duplicates, it will only keep the earliest timed one but I need both.
Hi 🙂 Please, How to compare between 3 columns or more to find and remove the duplicate data, to make each column unique from others. Waiting for your reply 😉.
Why doesn't excel offer the first option but instead of deleting them, highlight them? I need to find duplicates of only certain columns. Seems silly. Looks like my best option is to combine the columns I want to find duplicates for in your excel formulas option.
Excel-ent but let say with have a Row: of data and let's say its the alphabet. The only thing is I have 29 cells with data in them. Maybe 1 letter has been duplicated 3 times or 3 letters have been duplicated twice? How can I remove the duplicates so I end up with A-Z I have 17,000 rows of staff details to go through. The letters represent the departments they work for Corporate, Estates, Estates, Estates Management should result in Corporate, Estates, Estates Management (removing the duplicate of 1st instance ofEstates?
@@HowToExcelBlog Thank you for replying it was a bit... misleading. I can click on a column and remove duplicates but I cannot remove duplicates from a row?
*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-* ua-cam.com/video/tYvJ942YzQU/v-deo.html
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
JD i y
JD i y
8gf figgy
Yuh ui
Hi John.. thanks.. great summary of the ways to remove duplicate values. Just for fun, came up with my own way to do it with formulas:
Helper column E, cell E3, formula: =IF(COUNTIFS($B$3:B3,B3,$C$3:C3,C3,$D$3:D3,D3)=1,ROW(E3)-ROW($E$2)) - copy down to E12 to create a helper column index of the row numbers within the table with the unique records.
First extract column G, cell G3, formula: =IFERROR(INDEX(B$3:B$12,AGGREGATE(15,6,$E$3:$E$12,ROWS(G$3:G3))),"-") - copy down to row 12 and right to column I - extracts the columns and rows of data by matching the helper index number of the table to the data in the columns of the table. AGGREGATE screens out the error rows (those with FALSE). IFERROR puts a "-" if beyond the range of matches.. ready for new data, up to the point of copy/down.
The result simulates what you achieved with the FILTER function. I couldn't figure out how to get the row index directly into a resultant array to use within AGGREGATE, so the helper column is necessary. But, not a bad way to go if you want something dynamic and formula driven on the worksheet.. and you don't yet have FILTER. Thanks for the inspiration to tinker and create. Thumbs up!!
That was great having all those remove duplicate options in one video. Thank you.
Yes, there are quite a few ways! Do you know any more?
@@HowToExcelBlog I was looking one excel formula for unique list from 12 month columns list to one list with out blanks? SJT
UNIQUE with FILTER should do the trick if you have dynamic arrays.
on 12:10 solve my problem! Thank you so much sir!
Commentary I just awesome
you saved me so much time!! Thank you!!
Great, thanks for the tutorial
You're welcome!
Thank you.
You're welcome 👍
Excellent
Very complete! thumbs up!
Thanks for the tips.
No problem Luciano!
Good stuff!
Thanks!
I wanna remove the duplicates, but when we remove the serial no. changes and the blanks are gone.
I just wanna let those rows blanks from where the duplicates are removed.
Please provide me a solution.
can you help to trim and remove duplicate and text join using vba?. there are chances that the range I pull has duplicate value but does not get removed due to spaces in the end.so it should be trimmed and remove the duplicate.
Use power query to trim spaces first then remove duplicates.
I see VBA as a last resort. These days there are much better tools available for most things.
Super video.
Question- I have data in 6 column and data are same in all columns (data like- B200089NT67) when I select data range and apply condition formatting duplicate then all duplicate highlights but when go to duplicate remove option and apply then I find error "No duplicate find value" so how to solve it.
I had also checked all data extra space using Trim and also checked cell value is correct.
Example-
Column1 Colum2 Column 3
DR000257DT TR56884QR ASG489901 DR000257DT
Thank you I am going to need your help.
Hi David, I saw your email and have replied.
what if I have text in a cell, eg. A1 contains (abc abc vba vba cvba), How do I remove the duplicates or extract the unique ones?
WHAT can i do for a column with numbers that start with the same numbers but I want to identify duplicates with the last 4numbers
TYSM
You're welcome!
In formulas, instead of using the explicit reference to cells, I prefer to use the structured reference in the table. You used to fix cell E3, in the formula of the column «Count», the syntax =COUNTIFS($E$3:E3,[@Combined]), when the syntax of the table references could be used: =COUNTIFS(INDEX([Combined],1):[@Combined],[@Combined]).
I consider this type of reference more appropriate, but it is personal preference. 😁
No offense notice, please.
Hugs. 🤗
I noticed that the advanced filter results were different between the two methods. Can you please help me understand why/how filter in place and filter in a new location produced different results for the unique values?
The result is the same but some of the rows were hiden in the filtering process min 2:47
ua-cam.com/video/Lggwdf0AcYk/v-deo.html
I am having an issue with using VBA to try to remove duplicates from a specific column of data. Any time I define the column as a named range and try to delete the duplicates within the range, it keeps deleting duplicates outside the range. Here is the code:
With ActiveSheet
Dim rng As Range
Set rng = Range("Receipt")
rng.RemoveDuplicates Columns:=Array(1), Header=:xlNo
End With
The "Receipt" named group is Column D on my worksheet. The macro deletes duplicates from Column A, where I do not want to have duplicate information deleted. How can I ensure that the macro is not looking at data outside the specific range?
Is there anyway to enable the 'remove duplicate' option while the excel is in protected mode.... that option is getting disable when we keeps the excel sheet as protected
how can you do the other way around? Having only what you have at the end and going back and not manually, but automated of course. And I'm not talking about removing duplicates, but just removing the values from cells that are duplicates.
I used your Vba macro code, its showing error message subscript out of Range. how to proceed?
Is there any way it can tell me in which cell/s the duplicate value is?
How to extract the data of duplicate IDs and show all separately in a sheet
If I have excel 2013, How do I remove duplicate value that is the same in one column but in different rows.
Smith 2013 9:00AM
Smith 2013 9:03AM
If I try to remove duplicates, it will only keep the earliest timed one but I need both.
Hi 🙂
Please, How to compare between 3 columns or more to find and remove the duplicate data, to make each column unique from others.
Waiting for your reply 😉.
Download Microsoft's Fuzzy Lookup add-in for Excel
how we can find duplicate name which is in different format ( may be one name is short form another one in full name) in excel
How do u remove duplicates from the same row? For example, paul hammers, paul hammers
All in one row..
You can copy paste special and transpose the row to column.
@@HowToExcelBlog will that not affect other rows where there are multiple names, all separated by a ','?
None of the way worked in my case. I have a large data file to filter the duplicate values. Please suggest
Why doesn't excel offer the first option but instead of deleting them, highlight them? I need to find duplicates of only certain columns. Seems silly. Looks like my best option is to combine the columns I want to find duplicates for in your excel formulas option.
How do you keep only duplicates
I recall there's a keep duplicates command in power query.
How can find duplicates on iPad
Excel-ent but let say with have a Row: of data and let's say its the alphabet. The only thing is I have 29 cells with data in them. Maybe 1 letter has been duplicated 3 times or 3 letters have been duplicated twice? How can I remove the duplicates so I end up with A-Z I have 17,000 rows of staff details to go through. The letters represent the departments they work for
Corporate, Estates, Estates, Estates Management should result in
Corporate, Estates, Estates Management (removing the duplicate of 1st instance ofEstates?
Sorry, don't really understand what you are trying to accomplish, but try the power query method along with potentially some other transformations.
@@HowToExcelBlog Thank you for replying it was a bit... misleading. I can click on a column and remove duplicates but I cannot remove duplicates from a row?
*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-* ua-cam.com/video/tYvJ942YzQU/v-deo.html
NICE