Removing duplicates keep the first value and the blank cells

[Steven1988]

Hi guys!

I am new to this forum. I'm having a bit of problem.
I am trying to remove duplicates but i need to keep the blanks and the top values.
This is the tricky part, the values recurring in a group and im trying to keep the top values on that group
For example:

initial:
amber
amber
amber
james
james
james
amber
amber
amber
katie
katie

Prefered result:
amber


james


amber


katie


It would be very helpful if you guys could help me on this problem.

Thanks!

 

[AlphaFrog]

Try something like this...

[color=darkblue]Sub[/color] Top_Of_Groups()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] i = Range("A" & Rows.Count).End(xlUp).Row [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Cells(i, "A") = Cells(i - 1, "A") [color=darkblue]Then[/color] Cells(i, "A").ClearContents
    [color=darkblue]Next[/color] i
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Steven1988]

Thank you alpha for your help.

This might sound stupid.. but i do not familiar on using VBA codes. Do you mind enlighten me on how to use this code?

Thank you!

 

[jmcdo]

hi there

apologies for resurrecting an old thread but I found this page via google and it has very much helped me with a problem which is causing me to tear my hair out at the moment. However, I was hoping someone might help me modify the code mentioned in this thread to fully reach my aim.

i have a dataset similar to steven1988 i.e. recurring values in a group and I wish to keep the top values. rather than 'ClearContents' of the duplicate values, I would like to move the duplicate rows to another worksheet, where I can then re-run the code keeping the top values in Worksheet 2 and moving the remaining values to Worksheet 3 and so on. So nothing will be deleted but just moved. I have typed out what i mean below:

Initial	Worksheet 1	Worksheet 2	Worksheet 3	Worksheet 4
1	1
1		1
2	2
2		2
2			2
3	3
3		3
3			3
3				3
4	4
4		4
5	5
5		5
5			5
5				5
6	6
6		6
7	7
8	8
8		8
8			8
8				8
9	9

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

is this possible?

Any help very much appreciated!