Matching 2 cells value on product code

[Excelnewbie001]

Looking for some solution code in Vba to match and display if there is an exact match of 2 cells with column VW values -values in red..if there is a match the product code must be written in column Y....sorry I did not write an additional column for Y. Inexample Product Code 50 and 55 have matches....so there Y columns would have had 50 and 55.Only 1 row must be checked on product code the row have the products codes values.Each row must be checked for a match on the product codes value. I am sure this can be done with a formula or vba function ? I have over 200 product codes and this will help me tremendously. Help will be greatly appreciated to achieve above Thank you
[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X Product Code
[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]40
[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]38
[/TD]
[TD]40[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD]48
[/TD]
[TD]50
[/TD]
[TD]51
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]58[/TD]
[TD]55[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD]55[/TD]
[TD]68[/TD]
[TD]60[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]60[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]78[/TD]
[TD]70[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]30[/TD]
[TD]88
[/TD]
[TD]75
[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]70[/TD]
[TD]88[/TD]
[TD]75[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]35[/TD]
[TD]50[/TD]
[TD]80[/TD]
[TD]55[/TD]
[TD]70[/TD]
[TD]75[/TD]
[TD]98[/TD]
[TD]80[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]40[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]60[/TD]
[TD]75[/TD]
[TD]80[/TD]
[TD]108[/TD]
[TD]85[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]45[/TD]
[TD]75[/TD]
[TD]100[/TD]
[TD]65[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]118
[/TD]
[TD]90[/TD]
[TD]58[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Heres a formula for Y1 and then drag down:

=IF(SUMPRODUCT(COUNTIFS(A1:G1,V1:W1))=2,X1,"")

If you need VBA you will need to provide the sheet name.

 

[steve the fish]

Or maybe this as it caters for the number appearing more than once:

=IF(AND(COUNTIF(A1:G1,V1),COUNTIF(A1:G1,W1)),X1,"")

 

[Excelnewbie001]

Hi Steve and thank you for your reply the sheetname is sheet1 thank you very much

 

[Excelnewbie001]

You are correct there will be possible more than 1 exact matches in the row.....This will be a timesaver of note thank you very much

 

[steve the fish]

There will be a multitude of ways to do this in VBA. Heres one:

Dim arr()
With Sheets("Sheet1")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    ReDim arr(1 To lr)
    For i = 1 To lr
        If Not IsError(Application.Match(.Cells(i, 22), Range(.Cells(i, 1), .Cells(i, 7)), 0)) Then
            If Not IsError(Application.Match(.Cells(i, 23), Range(.Cells(i, 1), .Cells(i, 7)), 0)) Then
                arr(i) = .Cells(i, 24).Value
            End If
        End If
    Next
    Range(.Cells(1, 25), .Cells(lr, 25)) = Application.Transpose(arr)
End With

 

[Excelnewbie001]

Steve this works but it will also see 100 85 as 85 100 as a match on 100 85......Just tested it unless I am doing something wrong here

=IF(AND(COUNTIF(A1:G1,V1),COUNTIF(A1:G1,W1)),X1,"")

 

[steve the fish]

Oh ok they need to be in adjacent cells in the correct order??

 

[Excelnewbie001]

There will be a multitude of ways to do this in VBA. Heres one:

Dim arr()
With Sheets("Sheet1")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    ReDim arr(1 To lr)
    For i = 1 To lr
        If Not IsError(Application.Match(.Cells(i, 22), Range(.Cells(i, 1), .Cells(i, 7)), 0)) Then
            If Not IsError(Application.Match(.Cells(i, 23), Range(.Cells(i, 1), .Cells(i, 7)), 0)) Then
                arr(i) = .Cells(i, 24).Value
            End If
        End If
    Next
    Range(.Cells(1, 25), .Cells(lr, 25)) = Application.Transpose(arr)
End With

Thank you so much.....let me test quickly.....I would like to add button now that I think so a macro will be an BONUS.Let me test quickly and report back to you thank you so very much for your help.

 

[steve the fish]

This version will only match should the searched for values be in adjacent cells in the correct order:

Dim arr()
With Sheets("Sheet1")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    ReDim arr(1 To lr)
    For i = 1 To lr
        myMatch = Application.Match(.Cells(i, 22), Range(.Cells(i, 1), .Cells(i, 7)), 0)
        If Not IsError(myMatch) Then
            If .Cells(i, myMatch + 1) = .Cells(i, 23) Then
                arr(i) = .Cells(i, 24).Value
            End If
        End If
    Next
    Range(.Cells(1, 25), .Cells(lr, 25)) = Application.Transpose(arr)
End With