Excel Help

[Winalot2007]

Hi,

I wanted to know if it would be possible to match a combination 2 sets of "2 Digit" numbers (Pairs) into 1 set of 3 digit numbers.

For example:

In cell range I4:I103 I may have a random group of 30 to 40 "2 Digit" Pairs ranging from 00 through 99.

In cell range M4:M103 I may have a random group of 30 to 40 "2 Digit" pairs ranging from 00 through 99.

My question is:

Is it possible to take the 2nd digit (right digit) from the first group in cell in cell range I4:I103 and match it to the 1st digit of any pair in cell range M4:M103 to create a 3 digit number(s) in another sheet or cell range?

If a pair in cell range I4:I103 has a zero (0) in the right digit (eg 10) I would like to match it up to any pair in cell range M4:M103 that has a leading digit of zero (0).

For example:
If cell I4 has the pair 40 and cell M17 has 08 I would like to match the 2 pairs and place the 3 digit combo 408 in another sheet. example sheet3.

In cell I5 is the pair 95.

In cell range M4:M103 I have 53 in cell M5, 51 in cell M23, 58 in cell M25, and 57 in cell M27.

So the list would be 953, 951, 958, 957

Thanks

 

[time*treat]

*note some really long lines may wrap and look like two lines, these usually have a '_' between them, make sure to keep that or merge them properly. Otherwise, the code will break.

Sub pair__merge()
'by time*treat ~ Sept 13, 2008'
'merge two pairs into a triple'
'if the lag digit on the top number'
'matches the lead digit on the bottom number'
'i.e., AB + BC = ABC'

Dim top__col As Integer, bot__col As Integer
Dim top__pair As Integer, bot__pair As Integer
Dim out__row As Integer, out__col As Integer
Dim top__key As Integer, bot__key As Integer
Dim bot__sng As Integer, triple As Integer

Const top__row As Integer = 9
Const bot__row As Integer = 13

Sheets("Sheet3").Cells.Delete
Sheets("Sheet1").Activate

For top__col = 4 To 103
out__row = 1: out__col = top__col
Sheets("Sheet3").Cells(out__row, out__col).Value = _
Sheets("Sheet1").Cells(top__row, top__col).Value
Sheets("Sheet3").Cells(out__row, out__col).Font.Bold = True

For bot__col = 4 To 103
If Sheets("Sheet1").Cells(top__row, top__col).Value <> "" And _
Sheets("Sheet1").Cells(bot__row, bot__col).Value <> "" Then

top__key = Sheets("Sheet1").Cells(top__row, top__col).Value Mod 10
bot__key = Int(Sheets("Sheet1").Cells(bot__row, bot__col).Value / 10)

If top__key = bot__key Then
bot__sng = Sheets("Sheet1").Cells(bot__row, bot__col).Value Mod 10
triple = Sheets("Sheet1").Cells(top__row, top__col).Value & bot__sng
out__row = out__row + 1
Sheets("Sheet3").Cells(out__row, out__col).Value = triple
Sheets("Sheet3").Cells(out__row, out__col).NumberFormat = "000"
End If
End If
Next bot__col
Next top__col
Sheets("Sheet3").Cells.EntireColumn.AutoFit

End Sub

 

[Winalot2007]

Thanks For The Code

TT,

I'm having trouble using the code at this line

top__key = Sheets("AddRecord").Cells(top__row, top__col).Value Mod 10

Here is the link to the Excel zipped file.

The pairs I'm trying to combine are in the Tab labeled "AddRecord"

Thanks!!

http://www.badongo.com/file/11337328

 

[time*treat]

(now, if we could just get the indentations to properly carry over to posting, this would be easier to read)

Sub pair_merge_B()

'by time*treat ~ Sept 13, 2008'
'merge two pairs into a triple'
'if the lag digit on the left number'
'matches the lead digit on the right number'
'i.e., AB + BC = ABC'

'AB from left col'
'BC from right col'


Dim left__row As Integer, right__row As Integer
Dim top__pair As Integer, bot__pair As Integer
Dim out__row As Integer, out__col As Integer
Dim left__key As Integer, right__key As Integer
Dim right__sng As Integer, triple As Integer

Const left__col As Integer = 9
Const right__col As Integer = 13

Sheets("Sheet3").Cells.Delete
Sheets("AddRecord").Activate

For left__row = 4 To 103

out__row = 1: out__col = left__row
Sheets("Sheet3").Cells(out__row, out__col).Value = _
Sheets("AddRecord").Cells(left__row, left__col).Value
Sheets("Sheet3").Cells(out__row, out__col).NumberFormat = "00"
Sheets("Sheet3").Cells(out__row, out__col).Font.Bold = True

For right__row = 4 To 103

If Sheets("AddRecord").Cells(left__row, left__col).Value <> "" And _
Sheets("AddRecord").Cells(right__row, right__col).Value <> "" Then

left__key = Sheets("AddRecord").Cells(left__row, left__col).Value Mod 10
right__key = Int(Sheets("AddRecord").Cells(right__row, right__col).Value / 10)

If left__key = right__key Then

right__sng = Sheets("AddRecord").Cells(right__row, right__col).Value Mod 10
triple = Sheets("AddRecord").Cells(left__row, left__col).Value & right__sng
out__row = out__row + 1
Sheets("Sheet3").Cells(out__row, out__col).Value = triple
Sheets("Sheet3").Cells(out__row, out__col).NumberFormat = "000"
​

End If
​

End If
​

Next right__row
​

Next left__row
Sheets("Sheet3").Cells.EntireColumn.AutoFit
End Sub

 

[Winalot2007]

Thanks TT

Thanks TT for all your work!!

 

[BRAZILLOTTO]

Hi,

I'm also interested in use the excel codes, I would like to know if anybody here have any wheel for 40 numbers with the match rate up to 80%, and without spend too much.

Thanks