Remove dupes from one column, then replace removed values from another with remaining

[abigail99]

I wish that I could give you an on-screen credit on the National Geographic Channel for helping me with this problem, but alas, I cannot! We do not have an Excel expert on staff here, so I am appealing to you all for some help.

I have an Excel file with two pertinent columns. (There are more columns on the worksheet, but the data in them is not germane, and is different for all the rows.)

Column 1=ID# and Column 2=Title

IS031223 Dog Whisperer - Season 6
IS032679 Dog Whisperer - Season 6
IS032838 Dog Whisperer - Season 6
IS034803 Dog Whisperer - Season 6
IS035369 Dog Whisperer - Season 6

There are multiple ID#s erroneously assigned to the exact same title on different rows, and that is the problem. What I need to do is find all the instances where the title is exactly the same, and remove all the duplicates leaving one title and one ID#. It doesn’t matter which ID# is left, it could be the highest number, or lowest number, or one in the middle. In the example above, 4 rows would be deleted, leaving only one row. So far so good, I could even do this manually just by deleting dupes.

But then, all the old ID numbers that were removed need to reference the new single ID# in another worksheet. In the example above, I need to delete every row EXCEPT for IS031223. Then on another worksheet, any and all instances of IS032679 or IS032838 or the 2 others need to be replaced with the new single ID# IS031223.

Any guidance you could give me on how to accomplish this would be greatly appreciated.

Thanks,
Abigail

[BenMiller]

Will IS031223 ever be assigned to Dog Whisperer - Season 6 more than once in your table?

[BenMiller]

Ok, here's a solution. I can't write a macro to do the whole thing (I don't know much about VBA), so there will be some manual work on your part - but this should do the job.

Let's say your data is in A1:B10, with the numbers in column A and the titles in column B.

In C1, type =A1 and drag down, so that column C is a copy of column A.

In D1, type =INDEX($A$1:$A$10,MATCH(B1,$B$1:$B$10,0)) and drag down. This will give each title (still duplicates) the first number which appears for that title.

Now, copy/paste values, in place. Don't remove duplicates yet.

Highlight columns C and D and copy them. Go to that other sheet - the one where you'd like to replace the old ones for the new ones. Find a location on that sheet with no data in it. All you need is two columns right next to each other. Say columns I and J are empty, paste C and D (from the first sheet) into the second sheet, but make sure to start from the second row. So in our example, with 10 rows of data, you would paste it into I2:J11.

Now you have a lookup table with the old numbers on the left, in column I, and the corresponding numbers in column J.

Say your data which you'd like to replace (on that sheet) is columns A:G. Hit ALT+F11. Click Insert>Module and paste this macro (written by Smitty) into the module:

Please Login or Register  to view this content.

Replace A:G and I and J to reflect where your data is.

Hit F5, and run the macro, called ReplaceStuff.

IMPORTANT: This will permanently change your data and CANNOT BE UNDONE so make sure to test it first on a copy of your workbook.

Now you can go ahead and delete columns A and C from the first sheet, and remove duplicates. You'll want to change the order of the two remaining columns.

[Cutter]

Hello abigail99, and welcome to the forum.

Thanks for amending your thread title.

[abigail99]

Ben, thank you!! I am going to try this right now.

[BenMiller]

Ok, just remember to do it in a copy of your workbook, because this macro cannot be undone.

[abigail99]

Ben, thanks so much-- this worked perfectly!!! Exactly what I needed.

[BenMiller]

No problem Glad I could help ... (especially such a prestigious client!) Appreciate the feedback!

[Cutter]

@ abigail99

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.