Removing duplicates keep the first value and the blank cells

Steven1988

New Member
Joined
Mar 10, 2014
Messages
2
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try something like this...

Code:
[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

New Member
Joined
Mar 10, 2014
Messages
2
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

New Member
Joined
Sep 25, 2014
Messages
1
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 1Worksheet 2Worksheet 3Worksheet 4
11
11
22
22
22
33
33
33
33
44
44
55
55
55
55
66
66
77
88
88
88
88
99

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

is this possible?

Any help very much appreciated!
 

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top