Matching Data with Specified Criteria

[crapmind]

I need to match data with specific criteria. Details of the problem are as follows:

I have 2 sheets: "Main" and "Sub"

In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".

Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.

Now the specific criteria:

If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".

This is basic requirement.

I am unable to retrieve the right value for the same.

Fine tuining will be:

If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".


Can this be done??

Please help.


Thanks in advance.

[T-J]

A For Each Next loop, the Find method, the Range object, the Offset property and If Then Else statement are the basics of what you need; there are some examples of each in VBA help.

[crapmind]

Hey, i tried all this.. i m not getting the desired result.. otherwise why should i write in this forum??

[crapmind]

Hi, Can any one provide help on this?? i m really stuck on this

[rylo]

Hi

Can you create an example file showing your structure, some sample data, the expected output for some examples of your sample data and any code you have currently put together to date.

From what I can see, the response from T-J covered the basics of what you would need to make this work...


rylo

[crapmind]

I will surely post some data and code for your info very shortly.

Yes TYJ has given all the basic info. but the problem is i am unable to find desired result.

I will attach a file very shortly in this forum.

[crapmind]

Hi please find the attached file. The macro I have designed is not giving me the desired result. Please provide your valuable comment on the same.

Thanks in advance,

Crapmind

[rylo]

Hi

Here's a start. I've not done any conversions on the output at this stage, as the example sheet didn't seem to match the original post details. If you still want this conversion, can you give details of source / converted output.


rylo

Please Login or Register  to view this content.

[crapmind]

Thanks a lot rylo.... u r a champ dude

I need two up gradation in the same....

1) If Value in Column F of the main sheet <> 0, then the macro should not pick any value from Sub sheet. In other words, macro should only work when Value in column F (Main Sheet) = 0 and value in column column I <>0.

Can this be done????

2) The conversion Issue:

Any value in Column AF (Main Sheet), text which contains "Reuters ....." (i.e. Reuters Close, Reuters Tibco.... any value which contains the word Reuters) should be translated to "Reuters Last" and any Other Value (where there is no reuters) will be converted to "Bloomberg Last".

[rylo]

Hi

Here goes

Please Login or Register  to view this content.

rylo

[crapmind]

Thanks a lot dude...

[crapmind]

Hey Rylo,

Thanks a lot for all your help..

I need to upgrade the same code with few major changes.. here it goes...

Macro should work fine for any value in column C which starts with "D", but for other values it should check the following parameters:

It should calculate the variance between Column G and Column H. If the variance is less than 2%, then the rest of macro should work. If it is not fulfilling the above criteria, then there should not be any value/comments column AF.

Can this be done with above code?

[rylo]

Hi

What about the existing test parameters in the code? Are they still relevant or are they overtaken by the additional requirements?

Is the last posted example file still relevant, or should you update to give examples that match the additional requirements.


rylo

[crapmind]

Hi,

Yes existing parameters will not change. These are the additional parameters. Additional parameters will not overtake the existing parameters.

I am attaching an updated example file for your info, which will show an example of additional requirements.

Further, there is one problem in the present code. It only work if I run the macro from the main sheet, If I run the macro from any other sheet, I dont get the desired output.

crapmind

[rylo]

Hi

See if this is it.

Please Login or Register  to view this content.

rylo

[crapmind]

Thanks a lot rylo, U solved my problem.

I want a bit improvement in the code, if possible,

I want to refresh the values in column AF each time the macro runs, but not all the values. I want to refresh those cells where value = "Reuters Last" or "Bloomberg Last"

Thanks in advance

crapmind

[rylo]

Hi

Why not clearout column AF and rerun the macro???

rylo

[crapmind]

Hi Rylo,

I can not clear the entire AF column, because there will be other values which will be difficult and time consuming exercise to reinstate them again.. This other values requires lots of human (intelligent) judgment to process the same. This other values will be manually inserted.

Thats y i dont want to refresh entire column of AF.

Hope u understand the problem

crapmind

[rylo]

Hi

Here's another version that will just action the data in column AF. Looks for Reuters Last or Bloomberg Last and performs the same tests / actions as the first run.

Please Login or Register  to view this content.

rylo

[crapmind]

Hi rylo,

Thanks a lot for the effort. But I believe you are not clear on the problem.

Ur previous solution was absolutely fine. No issues with that. As an enhancement, I just wanted to refresh the data if any value in column G changes. For example, in row no. 6 value in column G ( 3.0) and H (3.05) is different. That is the reason there is a value in column I (0.05). Ur previous macro was picking up the value accurately, was giving value "Reuters Last" in column AF.

Now if I change the value in column G to 3.05, then the value in column I becomes "0". I want to re-run the macro at that stage. At present it is not refreshing the data. So value in column AF6 remains "Reuters Last".

So, I want to customise the macro. At the beginning, it should refresh only those cells in column AF, where value = "Reuters Last", "Bloomberg Last".

I hope u got the problem.

Please let me know if you have any query

crapmind

[rylo]

Hi

That is what the new macro is supposed to do. Go through all the entries in AF, and if it is a Reuters or Bloomberg, then re-test.

What is it not doing?

Or are you changing a line that doesn't already have a Reuters / Bloomberg result?

Update your sample file, advise what changes to make, what should the result be etc....

rylo

[crapmind]

Hi Rylo,

The macro is only working when there is "Reuters" or "Bloomberg" in column AF. If it is not

there the macro is not working. Becoz the condition u have given at the beginning.

At first, the entire AF column will be blank except few other values which are not Reuters/

Bloomberg. The Macro has to pick up values from sub sheet irrespective of any situation.

Later on if i again want to re-run the macro, the macro should refresh the AF column where

value is Reuters / Bloomberg. Then the macro should update the cells according to

predefined conditions and it should not update any other value where value <>

"Reuters/Bloomberg".

Hope u r clear this time.

crapmind

[crapmind]

hi rylo,

can pls help me on the same??

crapmind

[rylo]

Hi

I've added a line to clear out the existing Reuters / Bloomberg entry, then preprocess.

If this doesn't give you what you are chasing, then can you detail fully an example, what to change, what you expect to see etc....

Please Login or Register  to view this content.

rylo

[crapmind]

Hi rylo, I have divided the code in 2 parts.. now it is working... thats wat i was looking for... I have just modified ur codes... Thanks a lot man

Please Login or Register  to view this content.