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

A	A	B	C	D	E	F	V	W	X Product Code
38	40	10	20	25	30	35	38	40	50
4	10	15	30	30	35	40	48	50	51
6	15	20	40	35	40	50	58	55	52
8	20	30	50	40	45	55	68	60	53
10	25	35	60	45	50	60	78	70	54
12	30	88	75	50	60	70	88	75	55
14	35	50	80	55	70	75	98	80	56
16	40	55	90	60	75	80	108	85	57
18	45	75	100	65	80	85	118	90	58

<tbody>
</tbody>

 

[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