Show 3digits under Pairs

[wd8ekd]

A B C D E F
<table style="border-collapse: collapse; width: 387pt;" border="0" cellpadding="0" cellspacing="0" width="515"><col style="width: 54pt;" span="2" width="72"> <col style="width: 35pt;" width="46"> <col style="width: 41pt;" width="54"> <col style="width: 41pt;" width="55"> <col style="width: 54pt;" span="3" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt; width: 54pt;" height="19" width="72">1
</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 35pt;" width="46">
</td> <td class="xl64" style="width: 41pt;" align="right" width="54">12</td> <td class="xl63" style="width: 41pt;" align="right" width="55">66</td> <td class="xl64" style="width: 54pt;" align="right" width="72">18</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">456</td> <td align="right">123</td> <td>
</td> <td align="right">129</td> <td align="right">667</td> <td align="right">481</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">667</td> <td align="right">234</td> <td>
</td> <td align="right">123</td> <td align="right">366</td> <td align="right">881</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">129</td> <td align="right">112</td> <td>
</td> <td align="right">112</td> <td>
</td> <td class="xl65"> 018
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">119</td> <td align="right">312</td> <td>
</td> <td align="right">312</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">222</td> <td align="right">113</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">354</td> <td align="right">223</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">690</td> <td align="right">567</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">980</td> <td align="right">366</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19"> 481</td> <td class="xl65"> 018
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" align="right" height="19">433</td> <td align="right">881</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td colspan="7" style="">Need formula ??? We have a pair of numbers in D1,E1,and F1…..</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td colspan="7" style="">We want only the numbers that have 12 from column A and B</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td colspan="3" style="">66 from column A and B</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td colspan="3" style="">18 from column A and B only</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td colspan="3" style="">Hope I explained it right…..</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td colspan="2" style="">Thank You</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">
</td> <td>Bob</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[Legacy 68668]

try

Sub test()
Dim a, i As Long, ii As Long
Dim ColD As Long, ColE As Long, ColF As Long
With Range("a1").CurrentRegion
    .Offset(1).Columns("d:f").ClearContents
    a = .Resize(.Rows.Count * 2, 6).Value
    ColD = 1 : ColE = 1 : ColF = 1
    For i = 1 To UBound(a, 1)
        For ii = 1 To 2
            If a(i, ii) Like "*" & a(1, 4) & "*" Then
                ColD = ColD + 1
                a(ColD, 4) = a(i, ii)
            ElseIf a(i, ii) Like "*" & a(1, 5) & "*" Then
                ColE = ColE + 1
                a(ColE, 5) = a(i, ii)
            ElseIf a(i, ii) Like "*" & a(1, 6) & "*" Then
                ColF = ColF + 1
                a(ColF, 6) = a(i, ii)
            End If
    Next ii, i
    .Value = a
End With
End Sub

 

[kgkev]

Seiya

your code didn't do anything?

this is the data we are working on

Excel Workbook
	A	B	C	D	E	F
1	456	123		12	66	18
2	667	234
3	129	112
4	119	312
5	222	113
6	354	223
7	690	567
8	980	366
9	481	018
10	433	881
11	456	0466
12	0466	0466
13	121212	666666
14	458.18	123.18
Sheet1

 

[Legacy 68668]

Then try

Sub test()
Dim a, i As Long, ii As Long
Dim ColD As Long, ColE As Long, ColF As Long
With Range("a1").CurrentRegion
    .Offset(1).Columns("d:f").ClearContents
    a = .Resize(.Rows.Count * 2, 6).Value
    ColD = 1 : ColE = 1 : ColF = 1
    For i = 1 To UBound(a, 1) / 2
        For ii = 1 To 2
            If CStr(a(i, ii)) Like "*" & CStr(a(1, 4)) & "*" Then
                ColD = ColD + 1
                a(ColD, 4) = a(i, ii)
            ElseIf CStr(a(i, ii)) Like "*" & CStr(a(1, 5)) & "*" Then
                ColE = ColE + 1
                a(ColE, 5) = a(i, ii)
            ElseIf CStr(a(i, ii)) Like "*" & CStr(a(1, 6)) & "*" Then
                ColF = ColF + 1
                a(ColF, 6) = a(i, ii)
            End If
    Next ii, i
    .Value = a
End With
End Sub

 

[kgkev]

Still nothing doing

 

[Legacy 68668]

Hummm
How about ?

Sub test()
Dim r As Range, c As Range
With ActiveSheet
    Intersect(.Rows("2:" & Rows.Count), .Range("d:f")).ClearContents
    For Each r In Range("a1").CurrentRegion.Resize(, 2)
        For Each c In .Range("d1:f1")
            If r.Text Like "*" & c.Text & "*" Then
                .Cells(Rows.Count, c.Column).End(xlUp)(2).Value = r.Text
            End If
        Next
    Next
End With
End Sub

 

[kgkev]

Works now but doesn't get them all.

12 should pick up all the following

812
821
281
182
128
218

 

[Legacy 68668]

Hey you are not the OP.

I will wait for the response from the OP.

 

[kgkev]

yeah but I tested it and it doesn't do what the OP asked.

...218 would appear under 12 and also 18 column

So take the number 218 and test.

Does it appear under 12 & 18?

 

[kgkev]

mine picks up 162 under the 66 and I can't figure out how to ignore the 6 after it has been found once.

Perhaps your answer will be able to do this.

 

[Legacy 68668]

Ah, such way

Sub test()
Dim r As Range, c As Range
With ActiveSheet
    Intersect(.Rows("2:" & Rows.Count), .Range("d:f")).ClearContents
    For Each r In Range("a1").CurrentRegion.Resize(, 2)
        For Each c In .Range("d1:f1")
            If (r.Text Like "*" & c.Text & "*") + _
                (r.Text Like "*" & StrReverse(c.Text) & "*") Then
                .Cells(Rows.Count, c.Column).End(xlUp)(2).Value = r.Text
            End If
        Next
    Next
End With
End Sub