# Renumbering and deleting blank rows after finding and removing duplicates

1. ## 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!

2. ## 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. ## 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. ## 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. ## Re: Renumbering and deleting blank rows after finding and removing duplicates

Thanks a lot cantosh, it works perfectly!

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

#### 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