Hi,

I've been searching most of the morning for a solution to this (in hopes of making my life easier).

The situation:

I have two files that I merge then manually check for duplicate Customer Numbers. If I find a duplicate, I copy their Keycode from the old to the new. If there are multiple new records with the same Customer Number, I copy the Keycodeto all of the duplicates.

The file structure:

A = Keycode
E = Customer Number

Additional Info:

I have the file sorted by Customer Number so they line up and the new record(s) is above the old record.

Please help! The quicker the better! And thank you so much in advance!

EDIT: Picture

Hi,

I can't see any spreadsheet pictures in your post (there is only an advertising banner) so I'm going to improvise an example.
Btw - rather than linking an image to a 3rd party site, it would be much better to attach a workbook which we can experiment with.

Anyway...

The fact that they are sorted in Customer Number order, with the new above the old, makes life much easier. Say this is your starting point:
Row 1 contains the column headers "Keycode" and "Customer Number". The column letters A and E are only there as indicators, they are not on the worksheet itself.



Since you want to copy the old keycode to the new keycode, you could use an empty column to determine the correct keycode. Say column H is empty, put in this formula from the first record and copy downwards:
=IF(E2=E3,H3,A2)

It would look like this:
Then you can simply copy and pastespecial values from column H into column A and clear the contents from column H. The final result would be:
So that's a pretty straightforward way of achieving this. Of course you could automate the process using VBA, but if you are not familiar with VBA then I think that this is a more appropriate way for you to do it.

Hope that helps...

Thanks for the reply and I'll re-upload the picture soon.

However, my list contains over 32,000 records so the most appropriate way is to use a macro to copy the old keycode to the new (empty) keycode cell.

For a quick example:

And I need it to look like:

And I need to do that for thousands of records. Currently I highlight the "new" list and merge the two files, then go through it manually, copying and pasting.

Hi,
The fact that there are 32,000+ rows does not mean that VBA is the most appropriate approach. If someone here writes the VBA code for you then what will you do if it stops producing the results you expect? I guess you wouldn't be able to maintain it? So my line of thinking is that VBA might just not be the most appropriate way for you to do this...?

Now you have shown your example, isn't it just a case of filling in the blanks with the keycode from the row below? Adding a formula into a helper column, filling down the column and then copying and pasting values is a 2 second job, the logic of which is very similar to the improvised example I provided earlier. I don't see any issues with implementing it? Perhaps some problems may become clearer when you attach a workbook for us to see...?

Hope that helps...