Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding pairs of numbers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding pairs of numbers
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/200...e-elses-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Iriemon" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding pairs of numbers
Thanks Bernard!
It works perfectly! "Bernard Liengme" wrote: 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/200...e-elses-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Iriemon" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding pairs | Excel Worksheet Functions | |||
How do I graph x,y pairs of numbers with Excel? | Charts and Charting in Excel | |||
finding what numbers are in a string (Day 2) | Excel Worksheet Functions | |||
finding what numbers are in a string | Excel Worksheet Functions | |||
finding numbers | New Users to Excel |