+ Reply to Thread
Results 1 to 5 of 5

Remove duplicates and organize rows.

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Remove duplicates and organize rows.

    Im looking for some help creating some complicated module filtering code. The sheet will eventually contain a huge amount of data, so each time a row gets filtered the G cell will equal "yes" (that way only unfiltered rows will get processed, and the code will only run if unfiltered rows are present). The user adds new rows to the bottom, and then this code should filter them in.

    The code will organize the sheet EquipmentData in the following way:
    1- Remove any duplicated rows (from row 3 on), where Columns A and B match, giving priority to keep rows where Column E is not equal to "Manual". If neither have "Manual" in Column E then the higher row gets priority to stay. In cases where A is blank (and B matches) then it should only filter out if column C also matches (the same method of prioritizing applies).
    2- Then organize the rows (from 3 on) by Column A in ascending alphabetical order (there will be some cases where A is blank. These rows should end up all together at the top)
    3- When a row has been filtered in, the G cell should equal "yes".


    Below is the code I started with to check if any new records have been added to the sheet that need filtering, but the actual filtering part is over my head:

    Please Login or Register  to view this content.

    NB This sheet does contain conditional formatting which I want to protect.

    Thanks,
    James

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Remove duplicates and organize rows.

    This is not an easy undertaking. Normally I would say just apply advanced filter / uniques but I don't think that will not work in your situation because of the "Manual" in column E. RegEx might work but that is complicated in itself. An option would be to use advanced filter to get the uniques on a temp sheet, then autofilter based upon each unique. With each autofilter you would then apply your "Manual"/"top row priority". #2 would be using a sort but getting the blank rows on top is going to be a little challenging. #3 Who knows.

    I do not have any desire to work on this project, I have a feeling it will grow in scope, but figured I would give you my thoughts.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Remove duplicates and organize rows.

    Hi,

    Not entirely sure what you mean by item 3.

    Seems to me you need two macros. One to remove duplicated rows and then one to sort the data.
    I'd be inclined to use a helper column to decide if a row is a duplicated although I'm confused by your reference to identifying a duplicated row if the cells in column A & B are equal. Do cells in column A & B identify duplicates with other rows?

    Filtering data with a macro is quite simple. Just identify a two cell vertical criteria range where cell 1 contains the column label and cell 2 defines the value that identifies a duplicate. Then use something like

    Please Login or Register  to view this content.
    Once you've filtered the duplicate rows and want to delete them use something like

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Remove duplicates and organize rows.

    Thanks Richard, sorry its taken me a while to respond I've been out of town.

    I didn't explain how to identify duplicates very well. A and B will never match on the same row, but to search for a match throughout the sheet it should compare the data in columns A and B only. Columns C on may or may not match, but as long as A and B do then its considered a duplicate.

    With regards to item 3, basically Im going to have the code run as soon as the user navigates off the sheet (so its always kept organized, and the user is not aware of it). This sheet will contain a large amount of data, so checking all the data every time will take far to long. Hence the reason for the G cell equalling yes when the code has been ran on that row. That way before the code runs it can first check for cases where G is blank, that way if there is no filtering to perform it will move on a lot quicker. I have decided to hold off on Item 3 for now, as I may not need it. However if in the future it is required, then equally it would only attempt to filter the rows where G is blank, so it doesn't bother checking the rows that have already been filtered.

  5. #5
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Remove duplicates and organize rows.

    Any thoughts Richard?

+ 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. Code to remove duplicates and organize rows.
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2014, 07:23 AM
  2. [SOLVED] Find duplicates rows, sum cells then remove duplcate rows
    By Wagstaff in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-22-2013, 09:37 AM
  3. Remove rows without Duplicates
    By Apple1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2013, 10:12 AM
  4. Remove Duplicates from Rows
    By dwc in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-24-2013, 06:47 AM
  5. Replies: 1
    Last Post: 10-23-2012, 09:12 AM

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