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:
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.
Bookmarks