Microsoft Excel Tutorial: Excel Clear Duplicate Records but keep row there.
Welcome to another episode of Excel Hacks! In today's video, we're going to tackle a question from Michelle about removing duplicate data from a column, but without deleting the rows. This can be a tricky task, but fear not, I have a solution for you.
Michelle has a column with multiple dates and the same amount for each date. She wants to keep the amount in the first row, but clear the duplicates in the other rows without deleting them. So, here's what I would do. First, I would insert a column and name it "check". Then, I would use a simple formula to check if the name in the cell above is the same as the current cell. This will generate a column of true or false values.
Next, we'll turn on the filters and select all the rows with "true" in the check column. This will give us only the rows we want to clear. We can then use the shortcut Ctrl + Shift + Down Arrow to select all the cells with data and press the delete key. When we remove the filters, we'll see that the first row still has the amount, but the duplicates have been cleared.
Now, if your data is not sorted by company name, we need to add another step. We'll use the COUNTIF function to check if any of the cells above the current cell have the same company name. This will ensure that we only clear the duplicates for that specific company. And if there are any weird situations where different companies have the same amount, we can modify the formula to check for both the company name and the amount.
I hope this hack helps you in your Excel journey. Thank you to Michelle for sending in this question and thank you for watching. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time for another episode of Excel Hacks!
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduplicates #excelhacks
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
Table of Contents
(0:00) Problem Statement: Erase duplicate data without removing rows in Excel
(0:27) Formula to see if this customer is same as above
(0:45) Filter to True and clear amounts
(1:56) What if different amounts? Use AND function.
(2:30) Clicking Like really helps the algorithm
This video answers these common search terms:
how to clear duplicates but keep rows in excel
how to clear duplicate cells in excel
how to clear duplicate values in excel
how to clear duplicates in excel
excel clear duplicates and preserve rows
how to clear duplicates excel
remove duplicates but keep rows excel
delete duplicates but not entire rows excel
how to clear duplicates from a list excel
excel remove duplicates without losing data
Michelle asks this Excel question:
Is there a way to clear duplicate data in Excel but keep rows. Do not delete or remove the rows.
For example column A has company name. Column B has many dates. The amounts in column C are the same for each company. I need to keep the amount in the first row but clear the amounts in all the subsequent rows for that company.
Michelle has over 100K rows and manually deleting amounts is breaking her mind and body.
This video shows three solutions.
The easiest: data is sorted by company and the amounts never change.
Scenario 2: The data is sorted by date and not by company.
Scenario 3: One company can have two amounts.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
Excel skills:
Filter in Excel
Testing if this row is the same as the row above in Excel.
Topics:
how to clear duplicates in excel
remove duplicate numbers in a column in excel
remove duplicated from a column in excel
remove duplicates using a excel formula
how remove duplicate in excel
