Match words in 2 columns 2 sheet

[abjac]

HI. I have 2 named sheets. First in "Spain and the other is "Temp".
I need a macro which can compare the words in Sheet "Spain" column A8 and down with words in Sheet "Temp" Column H8 and down.
When there is a match it have to copy from sheet "Temp" Column J to Sheet Spain Column B in the same row where it find the match from the other columns,

So example If i have a match Sheet "Spain" Cell A8 with the word in Sheet "Temp" Cell H8(could be another cell). It have to copy word from from Column J same row to the match row and column B in Sheet "Spain2.

I have attached a test sheet so maybe better to understand there.

Please have a look

Sincerely

Abjac

[AB33]

Formula in B8 and drag it down

Please Login or Register  to view this content.

[abjac]

HI AB33 and thanks but it gave me this. Name in all cells. I use xp 2003 could make a difference. I would also really like a macro, because it will be a part of more sorting. If time please have a look at my test sheet.

Thanks

Abjac

[AB33]

I will convert in to macro, but I am not sure what an output you are expecting. For e.g, in B9, you get 1 which is LC*

[AB33]

I am still waiting a reply to post # 4 before I post the amended code.
Oops,
You are in excel 2003 which does not have Iferror function.

[abjac]

HI AB33. Yes this is correct it will be a 1. Yes i think could be the reason because i use excel 2003.

Thanks

Abjac

[AB33]

I have to admit that the last time I used the ISNA function was 10 years ago. The code works. The first line commented works in 2007

[abjac]

HI AB33. You nearly make me feel very old hehehe. 10 years ´maybe i should soon upgrade. The macro works perfect. Just one little thing can it be changed so it dont show the zero in column B where there is nothing. Would be great because i have to work with the data there with other macros.

If time have a look at the last. Thanks allot
Sincerely

The old Abjac hehe

[abjac]

HI AB I solved the problem with the zero with below code. Maybe not elegant but it works fine. Thanks allot for providing the first code to solve this. Like always great help from you.

SIncerely

Abjac

The code with delete zero also.

Option Explicit

Please Login or Register  to view this content.

[AB33]

Abjac,
There is no need to another add another lines of code.
Change this line
F

Please Login or Register  to view this content.

The red shows if there is no match, put it 0. You can change it to what ever you like
For instance, you can put nil values

Please Login or Register  to view this content.

[abjac]

HI Ab33. I tried change the code but it still give me the zero. Maybe again the old excel 2003?
Sincerely
Abjac

[AB33]

If there is no match, what value do you want to put in instead. It could be blank, 0 or some other text

[AB33]

Amended code.

[abjac]

HI AB33. Just tried it i tried that my self with replace function. But its not good it remove also zero in other numbers where there is a value. example customer bank account miss a zero in the middle. But its ok i can use the code i put in. So no need for you to work more with this, I am happy for the code you provided and my clumcy code infact do it with the zeroes,

Thansk allot for you efford, really appriciated.

Sincerely
Abjac

[AB33]

Abjac,
There was a bug on the replacement line, It has to include the whole match. Try it now

[abjac]

HI AB33. Yes of course it makes sense. It work like a charm now. Thanks allot for all the efford, really appriciated.

Have a nice day
Sincerely
Abjac