Solved

Excel, clearing contents based on data in the cells, reording data

Posted on 2011-03-02
8
257 Views
Last Modified: 2012-08-13
A sample file is uploaded and I need all multiple appearing names to be cleared from their cells [leaving one occurance of each name].
The remaining unique names to be in the uppermost cell of its section...this is the part that I can't get.
The dup names under the first one -- these cells  should have their contents cleared.
Other column need to remain as-is. [No row deletions].
In the attached sample, for example, the name should appear on row 2 and be deleted from row 3-5.
[I tried removing dups in A column using Trim + Count, but the remaining Name is in the last row of its section
instead of the top row of its section.]  

All help appreciated.
test-data-for-deduping.xlsx
0
Comment
Question by:dkoliver
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 35026089
is it acceptable to sort the date first ?

paste this code into a module.  Run with the desired sheet active.
Sub RemoveDup()
'

    Dim rngAll As Range, rngSort As Range
    Dim lastval As String
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Set rngAll = Selection
    Set rngSort = rngAll.Columns(1)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=rngSort _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rngAll
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Set rngSort = Range("A1")
    'lastval = rngSort.Value
    'Set rngSort = rngSort.Offset(1, 0)
    lastval = "===========" 'dummy
    Do While rngSort.Value <> ""
        If rngSort.Value = lastval Then
            'delete the value
            rngSort.Value = ""
         Else
            lastval = rngSort.Value
        End If
        Set rngSort = rngSort.Offset(1, 0)

    Loop
    
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35027775
Nice use of 'Sort' robberbaron. :)

dkoliver: I think this is what you want? Please check the sample file attached. I have colored the cells in 'Yellow' which will not be cleared? If that is correct, then run the Sub RemoveMultipleWords() in the Sample file attached.

Sid

Code Used

Sub RemoveMultipleWords()
    Dim i As Long, LastRow As Long
    
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LastRow To 2 Step -1
        If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A2:A" & LastRow), _
        Sheets("Sheet1").Range("A" & i).Value) > 1 Then
           Sheets("Sheet1").Range("A" & i).ClearContents
        End If
    Next
End Sub

Open in new window

Test-data-for-deduping.xls
0
 

Author Comment

by:dkoliver
ID: 35028434
Yes, thanks, I should have said sorting the file is fine.  I will try this today and let you know.  Thanks so much!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35028458
dkoliver: Did you see the code that I posted? You might not need to sort...

Sid
0
 

Author Comment

by:dkoliver
ID: 35031278
Yes, thank you, I did see your code and prefer it since it is shorter. I have been left behind in the excel world -- pls. explain how to do this programming -- do I enter as a macro somewhere on the sheet and run it?  Sorry to ask but it's been a long time -- back to Lotus 1-2-3 since I did any macro writing. Thanks!
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35031333
>>do I enter as a macro somewhere on the sheet and run it?  Sorry to ask but it's been a long time -- back to Lotus 1-2-3 since I did any macro writing. Thanks!

Please create a module and paste the code in the module.

Press Alt +F11 from the worksheet and in the VBE and check the module that I posted. You can then run the macros.

Perhaps later you might want to see this link to refresh on VBA :)

http://www.excel-vba.com/

Sid
0
 

Author Comment

by:dkoliver
ID: 35031641
Thank you so much!
0
 

Author Closing Comment

by:dkoliver
ID: 35032099
Very elegant way to handle the issue.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Deploying SCCM as part of Office 365 subscription ? 14 65
Exchange 2007 SP upgrade 9 97
connecting advanced find with a workflow 3 71
how to fix the excel 3 107
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question