Delete Rows that are the same as row directly above

Philly7040

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a data set of about 400,000 rows. I would like to delete every row where the value in column C is the same as the value in column C of the row directly proceeding that row. How can I do this?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,618
Office Version
  1. 2016
Platform
  1. Windows
What if you have like 3 rows of same value? You found the cell value the same as the one above. You delete the current row. When you proceed to next row, you will see same condition again and delete current row. So, you will end up with just 1 row out of 3 similar cell value in the same column?
 

Philly7040

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
What if you have like 3 rows of same value? You found the cell value the same as the one above. You delete the current row. When you proceed to next row, you will see same condition again and delete current row. So, you will end up with just 1 row out of 3 similar cell value in the same column?
Yes. In this case I would want to be left with just one row if there were thee rows with the same value in a row.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
Could you have the same value multiple times in col C, but not consecutive? If so should they all remain?
 

Philly7040

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Could you have the same value multiple times in col C, but not consecutive? If so should they all remain?
Yes. None-consecutive rows should remain. I only want to delete a given row of the value in column C directly above is the same.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
Ok, with 400,000 rows the quickest way will be to sort the data so the duplicates are together.
Do you have a header in row 1 with data starting in A2?
Is the number of columns always the same? If so what is the last column & if not can we use the header row to find the last column?
 

Philly7040

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, with 400,000 rows the quickest way will be to sort the data so the duplicates are together.
Do you have a header in row 1 with data starting in A2?
Is the number of columns always the same? If so what is the last column & if not can we use the header row to find the last column?
I do have a header in row one with the data starting at A2.
I do not want to sort the data to put all the “duplicates” together. That would defeat the purpose of what I am trying to do.


What I want to do is: have the computer look at each row, look at the row directly above, and if the row above contains the same value as the row above in column C, delete (“delete” not clear) that row.

What I want to be left with is just the rows that are the first row in this sort that contain a given value in Column C.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
I wasn't very clear in my explanation, after sorting the data to put the rows for deleting together they will then be deleted. That will be a lot quicker than deleting 1,000s of non contiguous rows.
Could you also answer the last part of my question.
 

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
Can you insert an additional column of data, say in Column Z, and in that column insert formula =if(C2=C1,"Delete","") and copy it down, then filter the sheet on Column Z where the value = "Delete" and delete those rows. Then delete Column Z and remove filter.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
@Brunts With 400,000 rows that could take a long time to execute, which is why I suggested sorting first.
 

Forum statistics

Threads
1,141,707
Messages
5,707,988
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top