Delete duplicate data in Column I leaving just one unique row

[mingandmong]

Hi
I'm using excel 2016

i have a spreadsheet with 56,000 rows and in column I have i have a set of data that is duplicated
i would like to delete the duplicates... however i need to retain the very last row of the duplicate as an example i have highlighted in red below ( the data is just a mock setup)
Many Thanks in advance

ie

Column I

John
John
John
John
Peter
Peter
Peter
Frank
Frank

 

[Special-K99]

Formula solution, VBA solution, which?

What happens if the value is already unique? Does another value have to be created?

 

[Gerald Higgins]

Do you ONLY need to keep the LAST row of each duplicate ?
Or do you also need to keep some other rows ?
Is it important that this is the LAST row ?
If you are ONLY dealing with duplicates in column I, it shouldn't really matter whether you keep the first or last item.
But if there are related columns of data that are NOT similarly duplicated, I can understand why you might want to keep only the last one.
Also, do you need to do this only once ?
Or lots of times ?

 

[CsJHUN]

Hi MingAndMong

Like Special-K99 and Gerald said, too much way to start the solution.
For start i would order the whole table based on column i, then start a macro which compare the actual and the previous values in column i, if same then delete the previous row.

Other hand if you have some date in anywhere in the sheet, and this could be used(like you need the newest date, or the oldest), you should consider using the excel built-in duplicate remover.

Give us more info, and good luck

 

[footoo]

Sort by column I
In a blank column, put ascending sequential numbers
Sort by these numbers in descending order
Data Tools / Delete Duplicates (column I)
Sort by the numbers in ascending order
Delete the numbers column

If required, a macro could be recorded of the above steps.

 

[mingandmong]

Hi Special-K99
No another Value does not have to be created

 

[mingandmong]

Hi Gerald Higgins

I do need to keep the last row because i need the unique data in Column A,B,C,D,E,F,G,H & K

 

[Gerald Higgins]

OK, what I would do (and assuming you only need to do this once) is . . . .

Assuming your data is ALREADY sorted by column I, and so that the last row for each set of duplicates is the row you want.

Create a copy of your data, just in case this goes wrong, so that you don't lose your data.

Apply a helper column to identify the last row for each set of duplicates, like this, assuming your data starts in cell I2.
In some other column, say L . . .
=I2=I3
Copy this formula down as far as required.
You should now have a FALSE value in column L, for the LAST item for each set of duplicates (including sets containing only a single item), and a TRUE value for every other item.
Select all the formulas in column L, and use Copy, Past Special, Values, to convert the formulas to values.
Then select ALL your data (columns A to L), and use Data, Sort, using column L as the primary sort key.
This should bring all the TRUE / FALSE values together, and it should be simple to delete all the TRUE values.
Then if necessary, re-sort using column I as the primary sort key.

 

[mingandmong]

Hi Gerald

Thank you for your help with the helper column, i just need to add in If statement though to get true and false then sorted it again all sorted thank you

 

[Gerald Higgins]

You don't need an IF statement.
Try it without the IF, exactly as I posted, see what you get.