1. ## 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

2. 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

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

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

3. 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. 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. 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
•