+ Reply to Thread
Results 1 to 5 of 5

Renumbering and deleting blank rows after finding and removing duplicates

  1. #1
    Registered User
    Join Date
    03-10-2016
    Location
    Houston, TX
    MS-Off Ver
    2013/2016
    Posts
    6

    Renumbering and deleting blank rows after finding and removing duplicates

    I have a 5-column and 2000+ row table ("Original") with multiple duplicates. I am able to determine those duplicates and erase them putting them in another table ("Original w/o duplicates") but the "Joint" column needs to be renumbered and also the information below the blank rows to be moved up so it continues the sequence (see table "Filtered"). I really would appreciate the help to use a formula to do this, also any other suggestions are welcome. Thanks a lot!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Renumbering and deleting blank rows after finding and removing duplicates

    Do you need to keep and store the various stages, or are you just looking to transform your 'Original' table into your 'filtered' table? If you just need the resulting 'filtered' table, you can skip the formulas and just run 'Remove Duplicates' (in the Data tab) on C4:E13 of your original table.

  3. #3
    Registered User
    Join Date
    03-10-2016
    Location
    Houston, TX
    MS-Off Ver
    2013/2016
    Posts
    6

    Re: Renumbering and deleting blank rows after finding and removing duplicates

    I do not need to keep or store them, just getting the "Filtered" table. The idea is to be automatic that's why I need formulas. Thanks

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Renumbering and deleting blank rows after finding and removing duplicates

    If you're going to re-number the 'Joint' column anyway, you'd be advised to leave it alone when you remove duplicates. Just leave it as it was in the original and you'll have the ''joint" numbers you want at the end. For X,Y, and Z, enter the formula below in P4. It's an array formula, so once you've pasted it, hit Ctrl + Shift + Enter. Then fill down through column P and right through column R.

    =IFERROR(INDEX($J$4:$L$13, SMALL(IF(FREQUENCY(IF($J$4:$L$13<>"", MATCH(ROW($J$4:$L$13), ROW($J$4:$L$13)), ""), MATCH(ROW($J$4:$L$13), ROW($J$4:$L$13)))>0, MATCH(ROW($J$4:$L$13), ROW($J$4:$L$13)), ""), ROW(A1)), COLUMN(A1)), "")

  5. #5
    Registered User
    Join Date
    03-10-2016
    Location
    Houston, TX
    MS-Off Ver
    2013/2016
    Posts
    6

    Re: Renumbering and deleting blank rows after finding and removing duplicates

    Thanks a lot cantosh, it works perfectly!

+ 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. Finding and deleting duplicates.
    By 54ed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2015, 12:24 PM
  2. Finding & Deleting Duplicates in a List
    By dsciola in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-12-2013, 01:16 PM
  3. Finding and deleting duplicates
    By Myrkk in forum Excel General
    Replies: 4
    Last Post: 03-31-2011, 07:39 PM
  4. Finding duplicates and not removing
    By sentinela in forum Excel General
    Replies: 1
    Last Post: 06-02-2009, 10:43 AM
  5. finding data in duplicate rows, revising row data and deleting duplicates
    By MF Xrx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2008, 06:49 PM
  6. Finding Duplicates & Deleting the ROw
    By samtwilliams in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-19-2007, 05:07 PM
  7. Finding and Deleting duplicates in a column
    By Brian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2005, 11:06 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