Macro to correct cels in column using another column (or w/sheet in the same file) as base

[DiegoBoffa]

Hi everyone,

I have a question and need urgent help on this.

I have an excel file with lots of macros...

What i do need now is a macro that could use a specific column as base (in the same worksheet and file[this would be the best], or even in another file) to correct another column that may contain wrong words or erros (according and based on the base column) on the column i need to check. let me try to explain this better..


Column X *********************************Column Y
(column that needs to be corrected)**************(base column with the right words)

aumoone***********************************aumoon
auearth************************************aumars
aumarssrf***********************************ausaturn
ausaturnii***********************************aumercury
aumercury********************************** auvenus
auvenusios**********************************aupluto
aupluto************************************auearth


Does anyone knows how can i create a macro (or even a formula) that:

Would use "Column Y" as base to correct or point what is wrong on "Column X"?

I'd appreciate if someone could try to help me on this...This macro is killing me and i'm about to go mad cuz of it hehe..

Thanks and best regards for all

[rylo]

Hi

Use match or countif on column X and see if the item exists in column Y. If not, then you have a problem to correct.

rylo

[DiegoBoffa]

match or countif? is that a formula? Thanks for helping..but i dont understand much..Can you explain it with the formula?
Thanks

[rscsmith]

Hi Diego

You shouldn't pester on other people's threads as it just confuses matters and pointlessly adds to the number of posts in the forum. Only post on other threads if your comment is relevant. Generally, if you have asked a clear question that is technically possibly to solve, all that is required is a little patience and someone will answer.

To add to rylo's answer above, here is a macro:

Please Login or Register  to view this content.

I have assumed that any error in the name is always on the end of the value (e.g. aumoone instead of aumoon).

Best regards, Rob.

[DiegoBoffa]

Thanks for helping and sorry to bother Rob..It was not my intention..The fact is that i have to make a demonstration tomorrow to my staff and this macro is really killing me and stucking me.
But really sorry about it.

Thanks for helping...But the problem is..the error sometimes is not on the end of the word..in that case what should I do?
I needed something to use the "Column Y" as a base to correct or at least point where the error is/are on "Column X"...

Thanks and regards

[DiegoBoffa]

What i thought i could do was to use a PROCH to check at least if the cells on "X" are in "y"(base)...but as far as know i can't use PROCH between 2 columns.. Any clue?

[rscsmith]

Hi Diego

rylo's solution will tell you where the errors are:

Put the following in cell Z1 and copy down.

Please Login or Register  to view this content.

Alternatively, put it as conditional formatting, to get a coloured highlight on the errors.

I haven't got any experience of how spell checkers work. You may be able to set up the list as a custom dictionary in Office, then use the built-in spell checker to do the wrok for you.

To do this, highlight column Y and copy the contents. Create a text file using Notepad (or similar) and save it as MyList.DIC

Next, highlight Column X. Press F7 to spell check. When the popup box comes up, press Options then Custom Dictionaries. Select Add and add your new .DIC file to the list.

Hopefully the inbuilt Office function will now sort you out.

Best regards, Rob.

[rscsmith]

You could also use PROCV as a test :

Please Login or Register  to view this content.

It would return #REF where X1 doesn't exist in the list.

You can't correct the problem with PROCH (aka HLOOKUP) or PROCV (aka VLOOKUP) is that the X list contains spelling mistakes. If you do a PROCV on the list, depending on where the error is in the misspelled word, you will get different results.

i.e. aumercuryadk would give a different result to aumorcury (you may get aumoon instead).

[DiegoBoffa]

Thanks everybody. I couldn't use any of your suggestions. I found another way to do it. But really appreciated your help guys.