# Excel delete duplicates based on previous row



## floggingmolly (Dec 14, 2022)

I'm not sure this is even possible, but I will try to explain the best I can.
I have a data set that lists Sent and Opened messages. The rows are sorted so they appear like below:

Message 1       Sent          7/11/2022
Message 1      Opened     7/11/2022
Message 2      Sent          7/12/2022
Message 2      Opened    7/12/2022
Message 2      Opened    7/13/2022
Message 3      Sent        7/14/2022
Message 3     Sent        7/14/2022

So, for example message 1 was sent and opened.  Message 2 was sent and shows opened 2 times. Message 3 was sent but not opened.  Also the messages can be sent to more than 1 recipient which is why message 3 shows sent 2 times.
If a message was opened 2 times, like message 2, I want to get rid of the duplicate row.  I can't do remove duplicates because then message 3 would get removed because it was sent 2 times.  
Is there a way to remove a row if the row above it contains the same criteria?  Like if Row 5 shows opened, and row 4 shows opened, then delete row 5?  

I hope this makes sense.  I'm not even sure anything like this is possible, but thought I would ask.


----------



## Kerryx (Dec 15, 2022)

Based on this logic


> Is there a way to remove a row if the row above it contains the same criteria?  Like if Row 5 shows opened, and row 4 shows opened, then delete row 5?


The second Messsage 3 would have to go as well as it is a duplicate of the line above it.

Is it only the "opened" duplicates you want to get rid of and is this all the data you have to do comaprison on. Any chance of using the XL2BB addin to give us some data to work with.


----------



## floggingmolly (Dec 15, 2022)

I tried installing the add in but it keeps giving me a message that macros were disabled and won't let me use it


----------



## klam (Dec 15, 2022)

My solution is to add a column to label the "duplicates", which are the rows to be deleted. The formula distinguishes if Columns A and B equal the previous row, as well as if Column B is "Opened".


----------



## klam (Dec 15, 2022)

Then Filter all the "Yes" in column D and you can delete them all at once


----------



## floggingmolly (Dec 15, 2022)

klam said:


> Then Filter all the "Yes" in column D and you can delete them all at once


I will give this a shot when I get to work tomorrow. I appreciate your help.


----------



## Peter_SSs (Dec 15, 2022)

floggingmolly said:


> I tried installing the add in but it keeps giving me a message that macros were disabled and won't let me use it


See if either of these help
xl2bb
Xl2bb got disable


----------

