Extract Digits only if match in 3 Ranges, (extract to next empty cell)

[frankyalta]

Hi,

i'm looking for a formula or vba code to extract matched digits from 3 ranges?,i been reading and searching (many examples online) but didn't found something close that can help me.
All digits ranging between 1 to 100
Range LR4:MM4 have 22 digits
Range MV4:NQ4 have 22 digits
Range NZ4:OU4 have 22 digits

In range KS4:LI4 (17 cells) is where i need to get the matched digits but coping/extracting on next empty cell in a row. (starting KS4)


Thanks !!!!!!!!!!!!!!!!!

 

[Tetra201]

... Please i need a Formula or VBA Code to Extract Digits Only if Matches in Range 1, Range 2 and Range 3 and copy on next empty cell in a row starting in cell KS4 to LI4 as displayed.

See if this code works for you:

Sub Extr3()
    r = 4: c = 305
    For i = 1 To 100 Step 1
        n = 0
        For j = 0 To 21 Step 1
            For Each k In Array(330, 360, 390)
                If Cells(r, k + j).Value = i Then n = n + 1
            Next k
        Next j
        If n = 3 Then Cells(r, c).Value = i: c = c + 1
    Next i
End Sub

 

[frankyalta]

Hi Mr FDibbins

what do you means??,
Range KS4:LI4 Have Digits 02-11-15-16-17-19-25-26-28-29-35, these digits exist on Range 1, Range2 and Range 3.
I don't really know, i might saying something but explaining on different way. (??)

Anyway Mr FDibbins, Thank you so much for your input.

Excel 2007

	KR	KS	KT	KU	KV	KW	KX	KY	KZ	LA	LB	LC	LD	LE	LF	LG	LH	LI

<tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]13-Jun[/TD]
[TD="align: center"]02[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

​

 

[frankyalta]

H Tetra201,

I have a Compile Error, Variable not defined.

Thank you so much Tetra201

Regards

 

[Tetra201]

... I have a Compile Error, Variable not defined.

Apparently, you module has an 'Option Explicit' statement.
Here is a modified code to address this.

Sub Extr3()
    [COLOR=#ff0000]Dim r, c, i, j, k, n As Long[/COLOR]
    r = 4: c = 305
    For i = 1 To 100 Step 1
        n = 0
        For j = 0 To 21 Step 1
            For Each k In Array(330, 360, 390)
                If Cells(r, k + j).Value = i Then n = n + 1
            Next k
        Next j
        If n = 3 Then Cells(r, c).Value = i: c = c + 1
    Next i
End Sub

 

[frankyalta]

Hi Tetra201,

Yes i have a Option Explicit on, The code worked !,

I have a question, Right now the code find/intercept/extract numbers that are in 3 ranges in row 4, About if in the future i need to do the samething but including row 5,row 6 and 7?
What line (or snippet) do i have to add or modify it?, it's just question...

Thank you! Appreciated!!

 

[Tetra201]

You are most welcome.

If you want to repeat exactly the same process for other rows, here is an updated code. The changes are in red.

Sub Extr3()
    Dim r, c, i, j, k, n As Long
    [COLOR=#ff0000]For Each r In Array(4, 5, 6, 7)[/COLOR]
    c = 305
        For i = 1 To 100 Step 1
            n = 0
            For j = 0 To 21 Step 1
                For Each k In Array(330, 360, 390)
                    If Cells(r, k + j).Value = i Then n = n + 1
                Next k
            Next j
            If n = 3 Then Cells(r, c).Value = i: c = c + 1
        Next i
    [COLOR=#ff0000]Next r[/COLOR]
End Sub

 

[frankyalta]

Hi Tetra201,

It work Perfect!!!

Thank you so much!


Regards