Results 1 to 5 of 5
Thread: finding pairings

20120426, 23:49 #1
 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 246 are various codes with associated locations, times, etc with col G being a concatenation of cols BF. In columns IR (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

20120430, 15:25 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20120501, 10:25 #3
 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

20120501, 11:26 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20120501, 11:32 #5
 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 1015 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