Excel Get Rid Of Duplicate Amounts, But Don't Delete The Rows! - Episode 2562

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!

#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

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.

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

[tumulakfam]

You are a life saver! My client wanted to display data like this. I didnt understand either why he wanted the 8 thousand lists to be like this but this video has saved me. Thank

[iamkool]

Very clever way of finding a workaround for this. Thank you very much

[khajashaik]

wow, you made my day... this is what exactly I was searching for... thank you. :-)

[RajeshKumar-mwzh]

That's nice video Sir, very helpful and now my work will be easy to delete the duplicate values without deleting the rows. thanks

[AliHaiderMalik-tngh]

Thanks a lot! I've been searching for this for so long. Google sheets pivot table can give this view if you add everything as rows. Nothing else i tried works till this

[shravanas]

Thanks a lot dear ..saved my time..🙏🙏🙏🙏

[KO]

COUNTIFS is the way to go but you can remove the filter step. Put the COUNTIFS inside an IF statement where if it is 0 then set it to the amount and if >0 set it to blank. Then copy the helper column and paste values into the amount column and delete the helper column.

[vishalwadekar]

What if you have double amount in another coloum like coloum A1 having 50 and column B having 100?

[matthewmckenzie]

Why is there no function to do this automatically? QMF had this on the mainframe three decades ago. They could do it in Access? Why not Excel? All I want is a reduced echelon outline similar to crystal reports. So hard and Google is full of, well, you know...

[ricos]

Interesting case! The question that always arises in these types of situations, is: why? I know that your example here Bill is just data that you've chosen to represent the question, but it's probably quite representative of this type of question in my experience too. The problem with presenting data in this way, is it can be misleading (and often just incorrect). In your example there is a date column, which is the differential on every row, often it might be a change of description per row or something, and the total that is repeated on every row is likely a subtotal for that company (or a combination of one or more other criteria). By deleting the subtotal from every row but the first, you're - in data terms - effectively assigning that value to the top row (in your example, the total for 3M Company is assigned in its entirety to 2/22/2022), which is almost always incorrect. Inserting a row after - or before - each company and assigning the subtotal there would obviously read better and be accurate. I know that each case is different, and often you're dealing with external systems that require data presented in a certain way, but it'd be interesting to hear the "why?" as well as the "how?".