Finding pairs of numbers

I

Iriemon

Is this possible? I assume this will involve some VB coding also:

Part Num Associated Part Num
123 456
789 5623
8521 6352
456 123
5478 9821
444444 AT256
5623 789


Formula in "C" that would find the mate and insert a "Pairing" number
So that the 1st and 4th rows would have a 1
and the 2nd and 7th would have a 2 etc
 
B

Bernard Liengme

This subroutine will do it - change the range in first statement to suit you
needed

Sub tryme()
Set testrange = Range("A1:C20")
For j = 1 To testrange.Count - 1
testA = testrange(j, 1) & testrange(j, 2)
For k = j + 1 To testrange.Count
If testrange(k, 1) = "" Then Exit For
testB = testrange(k, 2) & testrange(k, 1)
If testA = testB Then
mycount = mycount + 1
testrange(j, 3) = mycount
testrange(k, 3) = mycount
End If
Next k
Next j
End Sub

New to VBA? See one or more ot these:
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"
http://www.contextures.com:80/xlvba01.html

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Jon Peltier's site:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

(General, Regular and Standard modules all describe the same thing.)

best wishes
 
I

Iriemon

Thanks Bernard!

It works perfectly!



Bernard Liengme said:
This subroutine will do it - change the range in first statement to suit you
needed

Sub tryme()
Set testrange = Range("A1:C20")
For j = 1 To testrange.Count - 1
testA = testrange(j, 1) & testrange(j, 2)
For k = j + 1 To testrange.Count
If testrange(k, 1) = "" Then Exit For
testB = testrange(k, 2) & testrange(k, 1)
If testA = testB Then
mycount = mycount + 1
testrange(j, 3) = mycount
testrange(k, 3) = mycount
End If
Next k
Next j
End Sub

New to VBA? See one or more ot these:
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"
http://www.contextures.com:80/xlvba01.html

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Jon Peltier's site:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

(General, Regular and Standard modules all describe the same thing.)

best wishes

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


.
Click to expand...
 
Top