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
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
Transcript of the video:
Remove duplicate amounts, but there's a twist. Today's question from Michelle.
She wants to clear duplicate data from a column.
But keep the rows and not delete or remove the row.
So she has something like this, like the company appears with multiple dates and the same amount in every cell.
Michelle needs to keep the amount in the top cell, but clear the amounts in the other cells without removing those rows. So here's what I would do.
I would insert a column here. I'll just call it check.
And we'll say this name equal to the name above us.
That's either going to generate true or false all the way down.
And then we go to the data tab, turn on the filters, and under check we want all of the trues.
All right.
So now that we have just the ones we want to clear, we can do control shift down arrow and press the delete key.
When we remove the filters, you see that we have the amount on just the first row.
The other rows remain there with the other dates, and we can remove the check column.
Now, a couple of things here. If your data is not sorted by company name.
then we need to have a way to check to see if any of the cells above us from A1 down to the cell right above us is equal to the current value. And I'm doing that with a count if function.
So we're going to look from A$ that says always start at the heading to A1 without a dollar sign.
The rail right above the cell is equal to the name in this cell.
And now we generate zeros and ones, and all of the things that are greater than zero are the ones we want to clear.
So open the dropdown, unselect zero, leave everything else, and then clear these amounts.
And I checked with Michelle and she says that the company name, all of the amounts will always be the same.
But what if there was some weird situation where there were two different companies and some of them had a different amount here?
Then you would want to change this formula to check for two things, to see if the company name is equal to the company name above, and if the amount and C2 is equal to the amount above.
And then that would keep both this version of 3M and that version of 3M.
It's removed duplicates, but it's worth a twist.
Thanks to Michelle for sending that question in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
She wants to clear duplicate data from a column.
But keep the rows and not delete or remove the row.
So she has something like this, like the company appears with multiple dates and the same amount in every cell.
Michelle needs to keep the amount in the top cell, but clear the amounts in the other cells without removing those rows. So here's what I would do.
I would insert a column here. I'll just call it check.
And we'll say this name equal to the name above us.
That's either going to generate true or false all the way down.
And then we go to the data tab, turn on the filters, and under check we want all of the trues.
All right.
So now that we have just the ones we want to clear, we can do control shift down arrow and press the delete key.
When we remove the filters, you see that we have the amount on just the first row.
The other rows remain there with the other dates, and we can remove the check column.
Now, a couple of things here. If your data is not sorted by company name.
then we need to have a way to check to see if any of the cells above us from A1 down to the cell right above us is equal to the current value. And I'm doing that with a count if function.
So we're going to look from A$ that says always start at the heading to A1 without a dollar sign.
The rail right above the cell is equal to the name in this cell.
And now we generate zeros and ones, and all of the things that are greater than zero are the ones we want to clear.
So open the dropdown, unselect zero, leave everything else, and then clear these amounts.
And I checked with Michelle and she says that the company name, all of the amounts will always be the same.
But what if there was some weird situation where there were two different companies and some of them had a different amount here?
Then you would want to change this formula to check for two things, to see if the company name is equal to the company name above, and if the amount and C2 is equal to the amount above.
And then that would keep both this version of 3M and that version of 3M.
It's removed duplicates, but it's worth a twist.
Thanks to Michelle for sending that question in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
