Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    finding pairings

    Attached is a spreadsheet that I need some help with. In rows 2-46 are various codes with associated locations, times, etc with col G being a concatenation of cols B-F. In columns I-R (actually R is not used but could have been) are numbers that pair up 2 rows based on criteria that are not important here. The numbers used to pair up two rows is not important either; I could have used letters or symbols that would be the same for 2 rows meeting the same criteria. However, a number/letter is only used for 1 pair of codes although a code can be paired with any number of other codes. Numbers seemed easiest to work with. In this case, there are 43 pairings but the number can fluctuate.

    Once a number is assigned to 2 codes, what I want to do is, for each number (1, 2, 3,...,43) associated wtih 2 codes, copy the 2 descriptions that correspond to that number. I just used an assignment in rows 51 and 52 to show the result.

    I tried doing some things with Match, Rows, and other functions, but nothing came close. Not sure is VBA is needed; that would probably make the task somewhat trivial.

    TIA

    Fred
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Fred
    Seems you haven't had an answer yet, so here is my solution:
    Use a custom function.

    I have defined one as :
    zMatch(what, where, nth, column)

    So, for the first match, in cell [C51] of your file you would put:
    =zMatch($A51,$I$2:$R$45,1,"G")

    ..and for the second match, in cell [G51] of your file you would put
    =zMatch($A51,$I$2:$R$45,2,"G")

    ..and then copy these formulas down to row 93 as required.

    If a match cannot be found, the function returns a small "x", but you can adapt this to anything you require (or leave blank etc)

    I have attached a file showing this using your data in your file.

    The custom function is:

    Function zMatch(what, where As Range, nth As Long, col As String)

    Dim zCount As Long
    Dim zFoundCell As Range

    On Error Resume Next

    zMatch = "x" 'set default value if not found

    zFind = what 'search value

    Set zFoundCell = where.Cells(1, 1)

    For zCount = 1 To nth
    Set zFoundCell = where.Find(what, zFoundCell, xlValues, xlWhole)
    Next zCount

    zAddress = zFoundCell.Address
    zRow = Range(zAddress).Row
    temp = col & zRow 'e.g. "G27"

    zMatch = Range(temp).Value

    On Error GoTo 0

    End Function

    Let me know if this works for you.

    zeddy
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Zeddy.

    I think the VBA way is the way to go. I would have thought it possible to do with some combination of the various search/lookup BIF's but I couldn't get it.

    Fred

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Fred

    Although I noticed that your 'pairings' seemed to always be in the same column (e.g. your "3"s are in same column), there is no simple formula combo of lookup, match,index etc that can easily search a 'block' of data rather than a single column.

    But the VBA custom formula seems simple to use in my opinion!

    zeddy

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    The fact that each pairing number (eg the "3") is in the same column is not an accident. I fill in the top part, where the codes are listed, manually to create the pairings. Since I was filling in the bottom part manually too, it seemed simpler to find the pairings if the pairing numbers were in the same column.

    In the past, I only had maybe 10-15 pairings. This time, I had 40+. So I thought it would be easier if I could create some way of using BIFs to do this. Certainly would be easier but not with the BIFs - glad to hear that I didn't miss something obvious.

    No problem with the VBA especially if it helps see the different pairings.

    Thanks again.

    Fred

Posting Permissions

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