1. match/fill (2003)

hi all,

The data I have is structured as in the attached file, an area no (column [img]/forums/images/smilies/cool.gif[/img] with a group of wells (column C), a Y flag for the key well (column E) and a completion code (column F) for each well. What I want, is assign a selection base code (column D) for all non-key wells based on two steps or iterations:

-Comparison of completion code (column F) of the key well with non-key wells. If there is a part or full match of completion code, then it should assign

2. Re: match/fill (2003)

You are going to have to explain a little better since doing what you requested, does not seem to match the values you give in Col D.

I used the array formula (confirm with ctrly-shift-enter) in D2
=IF(OR(E2="Y"),"",IF(TRIM(F2)="","A",IF(COUNT(IF(I SNUMBER(SEARCH(F2,\$F\$2:\$F\$24))*(\$E\$2:\$E\$24<>"Y"),1 ))>1,"AC","A")))

and then copied D2 to D324.

I got different values in :
I get "A", not "AC" in Col D since the values in F are not partial matches in the subset"
F9:= BURY & BURE
F16 = BURG & BURJ & BURM
F17 = BURY & BURY&BURE & BURE & BURG

I also get "AC" instead of "A" in D19 since F19 (BURY & BURC) is the same as F3 and in D21 since F21 (BURE) is a partial in F7

Steve

3. Re: match/fill (2003)

I think dubdub only wants to look for partial matches within wells in the same area (rows 2-4 is area 1, rows 5-7 is area 8, rows 8-9 is area 18 etc.)

4. Re: match/fill (2003)

Perhaps, but that would still not make the items in row 16 and 17 anywhere else to give them an "AC"...

Steve

5. Re: match/fill (2003)

The attached code uses a rather blunt approach; it iterates a number of times (specified by the constant 'Nauseam') through the rows to place the "AC" values, then iterates through them a final time for the "A" values.

6. Re: match/fill (2003)

I guess it's a matter of interpretation. I have posted a brute force approach that appears to yield the same results as dubdub indicated (except for an extra space after one of the "A"s).

7. Re: match/fill (2003)

You also were parsing within the objects within the code. This did not seem "intuitive to me" since if they were each separate, I did not see why a particular code should have duplicates in it.

Thus, my formula approach does not do that. It reads the codes as distinct.

I guess we will have to see how the OP responds....

Steve

8. Re: match/fill (2003)

many thanks Hansv,

i have tested the code with more data(attached file) and for unknown reason to me there were some mismatchs between what is expected and what the code shows, some may be related to the non-key well location relative to the key well(s) in the data. for example if well 181 which is a key well is placed prior to 179 there will be a match.

regards,

9. Re: match/fill (2003)

Hi Steve and many thanks,

I am no code expert, but seriously interested to know what you mean by example from the data.

Regards

10. Re: match/fill (2003)

I hadn't properly taken care of the cases where Y is not in the first row for an area. And you hadn't told us that there could be more than one key well in an area. The attached version takes these into account.

There are still some mismatches between the result of the macro and your expected result. I have marked these clearly, with an indication of the cause. I don't understand your expected result in these cases.

11. Re: match/fill (2003)

thank you Hans, i will test it with more data and i hope i will not come across more conditions, thanks again.

12. Re: match/fill (2003)

Since I do not recall using the phrase "example by data" I do not understand your question.

It seems though my thoughts/logic to the solution was wrong and that Hans' solution is more applicable.

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•