+ Reply to Thread
Results 1 to 10 of 10

Removing Duplicates between two cells reliant upon the formatting of the two cells

  1. #1
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Exclamation Removing Duplicates between two cells reliant upon the formatting of the two cells

    Hi All,

    I'm a bit of a beginner so this is out of my limits. Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.

    It feels like a kind of backward conditional formatting is what I am looking for

    Any advice would be amazing.

    Here is my code as it stands


    Dim FindString As String
    Dim Rng As Range
    FindString = Range("D2").Value
    If Trim(FindString) <> "" Then
    With Sheets("Milestones").Range("A:A")
    Set Rng = .Find(what:=FindString, _
    After:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)


    If Not Rng Is Nothing Then
    MsgBox "Project already exists in milestones - any additional milestones will be added"
    Sheets("milestones").Activate
    Rng.Select
    If LastWorksheet <> "" Then
    Worksheets(LastWorksheet).Activate
    End If
    Range("B19:F19").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Milestones").Activate
    Rng.Offset(1, 0).Select
    Selection.Insert shift:=xlDown



    Else
    Range("D2").Activate
    ActiveCell.Copy
    Sheets("Milestones").Select
    Range("A5").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    If LastWorksheet <> "" Then
    Worksheets(LastWorksheet).Activate
    End If
    Range("B19:F19").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Milestones").Activate
    Rng.Offset(1, 0).Select
    Selection.Insert shift:=xlDown

    End If
    End With
    End If
    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    After the line

    If Not Rng Is Nothing Then

    add this:

    Please Login or Register  to view this content.
    Then you can use the rngC object to look for duplicated values, like

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    Hi Thanks for the reply - my only concern is that I dont know what milestones the people will be adding so I cannot put a "milestone name" reference?

    would the second code provided mean each milestone would have to be copied and pasted individually? At current it copies a block of milestones, adds them and then i was hoping to remove duplicates after this point reliant upon the milestones being between two left indented project titles.

    I have no idea of any of the actual text that will be input for either the project title or any of the milestones

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    You can replace "Milestone Name" with a cell, or series of cells:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    At the end, it does not matter if you add the milestones as a block or not - you will need to loop through them to decide whether the value was there previously or not, so you can add each of them after deleting the old one - six of one, half-dozen of the other. Where are your milestones stored prior to adding them?

  6. #6
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    The milestones will be in a sheet which is specific to the project title.... currently i have sheet titled 'generic' which when a person populates the "add project" combobox, it copies the generic sheet, renames it according to the project title and populates part of it. The person then manually inputs milestones into cells B19 downwards. They then click the transfer milestones button and it takes the information from there across to the "milestones" sheet. The reason it is becoming so difficult for me is because this will all be populated after I issue this central document so I have no idea of what the titles will be, hence no idea of the sheet names and also no idea of the milestones. I had to search to find the code to reference the last active sheet to allow flicking between the page the person created (which becomes the current active project page) and the milestone page

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    On your 'generic' sheet you should have a button that calls the macro. Then, after that sheet is copied, the button will be on the new sheet as well. When the button is clicked, the macro is called, the currently active sheet is the sheet with the data, and you do not need to 'flick' between sheets. For example, assign this to the "Transfer Milestones" button

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 07-30-2014 at 11:16 AM.

  8. #8
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    The button is on the generic sheet and copies across and I will assign the correct macro. But the new sheet will have a name I dont know and I need to flick between that new imaginary sheet which will have been created and i cant reference directly to its name and the Milestone sheet which is always there.

    I will give that code a go anyway

  9. #9
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    The macro i posted at the top is the one to be assigned to the button, but as it stands I am likely to create duplicates if the person enters a few milestones one day and clicks it, and then goes back and adds more and clicks it again

  10. #10
    Registered User
    Join Date
    07-30-2014
    Location
    Derby
    MS-Off Ver
    2010
    Posts
    17

    Re: Removing Duplicates between two cells reliant upon the formatting of the two cells

    Can anyone offer any advice/help?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Removing Duplicates without moving cells up
    By Nickyh1984 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2014, 06:02 PM
  2. [SOLVED] removing duplicates in cells that don't comply with formula.
    By wyldjokre69 in forum Excel General
    Replies: 7
    Last Post: 09-16-2013, 06:54 AM
  3. [SOLVED] Remove duplicates without removing cells (leave blank)
    By RobertOHare in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2013, 09:57 AM
  4. Replies: 1
    Last Post: 07-15-2011, 08:36 AM
  5. [SOLVED] Sorting or if statements reliant on formatting
    By Andy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2006, 08:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1