Delete Rows that are the same as row directly above

[Philly7040]

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?

 

[Zot]

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]

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]

Could you have the same value multiple times in col C, but not consecutive? If so should they all remain?

 

[Philly7040]

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]

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]

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]

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]

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.

 

[Fluff]

@Brunts With 400,000 rows that could take a long time to execute, which is why I suggested sorting first.