Find match from 2 columns in 2 sheets and display result in the second sheet

**abjac** · 06-12-2013, 03:12 PM

Hi Have this project i am working on. I need to have a macro which can find the match Between Sheet Middle result column B and Sheet Middle Result1 Column A. And then Put the matching numbers from Sheet Middle Result column D into column B sheet Middle Result1.
I guess its a kind of Vlookup. But the very special is that it have to be country seperated.
The result i have in Sheet Middle Result, is a macro which Jindon so fantastic made. Where he sorted these date, from another master sheet, with allot of data.

Please have a look at the attached sheet. I am sure its more easy to see what i mean there.

Hope some can help with this.

Sincerely
Abjac

**AB33** · 06-12-2013, 03:31 PM

Try the attached.

**abjac** · 06-12-2013, 05:24 PM

HI AB33. Thanks for the sheet. I have tried and tested it, but it show the wrong result. If you look at sheet Middle Result. Its in the buttom in column D. I have calculated, how much there is in every country. In Denmark example there is a total of 58. And that match the first total amount in Sheet Middle Result1. But that amount continue all the way down. 58 Total in every country. Even the last 5 forms, which there is no county for, have a total of 58 items.In Sheet Middle result1 example United Kingdom should have a total of 151. But the result here is again 58.

So some must be wrong i think. It have to be country seperated. So i make the match for example Denmark. And do this seperate for all countries. And where there is not a match example in the last 5 forms. which have the header Disp. It shall not find anything.

So if you have time for solving this challange, it will be great.

Thanks

Sincerely
Abjac

**AB33** · 06-12-2013, 05:39 PM

I thought you said a simple Vlookup.

Please Login or Register  to view this content.

**abjac** · 06-12-2013, 05:43 PM

Smile. No i dont think its simple if i said that its wrong. I think its quite complicated to be honest(over my skills). So no simple absolute not .
So i hope its possible, but i think it is with a macro.
Forgot to say also the layout in sheet Middle Result is made only by a macro provided by Jindon. But the layot in sheet Middle Result1 or the form is made by me. Notice that i have example in column B made a kind of mark. I have example Denmark and have put an "End" , where it have to stop. Dont know if that can be usefull, but i guess. So the macro have to find the match between Denmark and End for the items from Denmark
But have a look.

Sincerely
Abjac

**AB33** · 06-12-2013, 05:56 PM

A formula would be simple to adjust. I have done the first two(Demark and UK), you can adjust the formula yourself for the rest. I need to doze-off now.

**abjac** · 06-12-2013, 10:48 PM

Thanks AB33. I think its working for sure. The thing is just that sheet Middle result can change. Its generated only by a macro and can change, depends of the input in the sheet, sheet middle resullt is made by. So thats why i am wurried with formulaes. Would really prefer a macre.

But thanks really hope some can provide the macro for it, would be great

Sincerely

Abjac

**abjac** · 06-13-2013, 12:10 AM

Hi AB33.

I tried this code below, it does the same as the formula. You can also see it in the new uploaded testsheet.
The problem is not the settings in the code for Sheet Middle Result1. They can be like this way.
The problem is the range for sheet Middle result.
Exsample for Denmark it set to

Please Login or Register  to view this content.

But this reference or range have to someway to refer to the Country Name (Denmark) in Column A and to last used cell in Column B.

If it can be done like this, it can be done and also the range of rows can expand or be smaller.
Then it will be great and the code who find the result in sheet Middle Result, will work exelent. Because it display how many catagories there is.
And as you can see from country to country ist different.
So to change the code

Please Login or Register  to view this content.

with reference to the Country Name in Column A and to last used cell in Column B, i think will work.

It can be done first for one country and then maybe see how it can be put together for all countries.

Below is the code i used to do macro for country Denmark and United Kingdom.

And there is also a new test sheet(forgot one catagory in the first)
So have a look.

Thanks in advance

Abjac

Please Login or Register  to view this content.

**AB33** · 06-13-2013, 03:36 AM

Abjac,
I know it can be done by a macro, but need to do for each region and will end up with 5-7 codes. When you change your data, you need to adjust the code too, hence I do not see the need for a code. You are returning a single value, so Vlookup will fit in. You do not need any help from anyone if it is a formula and can easily adaptable. If you insist for a code, you have already done most of it anyway.

**abjac** · 06-13-2013, 04:12 AM

Hi AB33. I think i need the code. The reason is that the sheet MIddle result can change.
Its taken from a bigger mastersheet and display the amounts of catagories for each coountry.
Right now example Denmark the match have to be found in Sheet Middle Result from range A4 to A27.
But lets say the next time i run the macro there is maybe only catagories for Denmark between A4 and A8. So that will change the range in all countries. Thats why i think i need a macro.

The code which provide this is quite fantastic from Jindon, because it infact can be variable.

So thats the explanation why i need a macro.
Hope i can explain it correct,

Sincerely
Abjac

**AB33** · 06-13-2013, 04:29 AM

It would be easier for Jindon to adjust his code if his code does what you are after.

**abjac** · 06-13-2013, 04:53 AM

Ye AB33 Thats maybe correct. lets see if he have time to pop up here. Thanks for your effort and your solutions

Sincerely

Abjac

**abjac** · 06-14-2013, 11:11 AM

To get a little more eyes on this thread i have cross posted it.
Can be seen here.
Sincerely

Abjac
http://www.mrexcel.com/forum/excel-q...ell-other.html

**abjac** · 06-15-2013, 05:59 PM

HI Rick Rothstein solved the problem in the other forum. I have to make alot of macroes, they can proberly be together, but i am not sure i know to do that.But below is the code for the first 2 countries. So it can work i think.

Will mark this as solved thanks for all the help also here.

Sincerely

Abjac

Please Login or Register  to view this content.

Find match from 2 columns in 2 sheets and display result in the second sheet

LinkBack

Thread Tools

Rate This Thread

Display

Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Re: Find match from 2 columns in 2 sheets and display result in the second sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions