Comparing lists, adding data, and color-coding changes

[Gobaith]

Hi guys,

Need your help really bad! Can't figure out the first thing I'm supposed to do here...

So here goes:

Sheet1 contains a huge list of unique text entries in CJK characters in column B. Sheet2 has updates for some of the entries (where column A contains the list of the entries - some of them identical to those in column B of Sheet1, but some different, and columns B, C, D contain new data).

Now, what I really need to do is to match entries in Sheet2!A:A against those in Sheet1!B:B , and

• where there is a match, I need for the accompanying info from columns B, C, D of Sheet2 to be copied into columns C, D, E of Sheet1 for every matched entry,
• where entries in column A of Sheet2 are unique, I need the entire rows to be appended at the bottom of the list on Sheet1 (starting with column B)

and it's absolutely necessary that all changes to Sheet1 are shown in blue.

Really racing against deadline here, so any ideas would be deeply appreciated!

Thanks in advance

[Pete_UK]

You can use formulae based on VLOOKUP or INDEX/MATCH to fetch the data across, but to show changes in blue you will need to use conditional formatting or a macro. Post an example file, and you'll get your answer more quickly.

Hope this helps.

Pete

[Gobaith]

You can use formulae based on VLOOKUP or INDEX/MATCH to fetch the data across, but to show changes in blue you will need to use conditional formatting or a macro. Post an example file, and you'll get your answer more quickly.

Hope this helps.

Pete

Thanks for a quick reply.

I can't post the original file, obviously, because the blasted thing is confidential, but here's a sample file with some dummy data. Hopefully, it'll give a clearer picture of what I'm trying to do.

[Pete_UK]

... here's a sample file with some dummy data. Hopefully, it'll give a clearer picture of what I'm trying to do.

Although I can open your file, I can't make any sense of it as it contains symbols which mean nothing to me (Cyrillic or Japanese characters ?)

Pete

[watersev]

hi Gobaith, welcome to Excelforum, please check attachment, run code "test"

[nilem]

another option

[Gobaith]

@watersev, @nilem

Guys, thank you so much for your time and effort. Your scripts do almost everything I need, just a few kinks remain:

@watersev,

Your script works like a charm on a sample Latin char-based list (see another dummy database attached), but overwrites Row7 Sheet1 in a CJK set-based file, generating 11 entries instead of 12. And it doesn't seem to mark the changes in blue.

Also, when I try it on the actual list - which is enormous (more than 50,000 entries on Sheet1, and 30,000 updates on Sheet2), the script generates a "Subscript out of range error" .

@nilem,

Your script does wonders for the CJK test file (really nice touch with the button, btw), but it also generates a "Subscript out of range" error when run on the real thing. And it generates another error when I try it on my English-language dummy database. Is it something that can be fixed?

Also, if it's at all possible, could you make sure that there is no limit on the number of rows in the columns that are being matched, because a lot of times updates are really huge - 50,000 entries or more.

Thanks again.

[watersev]

please check attachment, run code "test"

[Gobaith]

sorry, watersev, still no go.

When I run it on the sample list, the script generates an "Invalid procedure call or argument" error. After I hit "End", the entries are generated, except that appended entries are not in blue in column B.

When I try it on the actual file, after hitting "End" on the same error the new sheet is generated, this time without any unique entries appended.

[watersev]

I suppose it's different localization, I do get correct results. Try this option, please

[nilem]

or try it

Please Login or Register  to view this content.

[Gobaith]

@watersev, the script still generates the same error. Like you say, it must have something to do with the localization (my copy of Excel 2007 is the original English version).

@nilem, I can't thank you enough!

It finally worked! Your script took only maybe 15 seconds to update the entire database (while I was already seeing myself growing old sorting the damn thing by hand). I really don't know what I would have done without your assistance.

Thanks again!