Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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

    Either your sample answers are wrong or I do not understand your logic...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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,
    TIA
    dubdub

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    TIA
    dubdub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    TIA
    dubdub

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •