Code To Look At Column C And If All Data Matches In Column AC Copy To Sheet 2

[Dazzawm]

I have a file as laid out below. I need the code to look at column C and if the data matches then look at column AC. If all the data in column AC says 'NYA' then copy to sheet 2 like the second example. Thanks.

Excel 2010

	C	AC
CatCode	StockCode
AAROC18 3001	NYA
AAROC18 3001	NYA
AAROC22D 3001	NYA
AAROC22D 3001	NYA
AM40004D 1001	NYA
AM40004D 1001	NYA
AM40004D 1001	NYA
AM50005 1001	NYA
AM50005 7001	TEST
AM50005 7001	NYA
AM50005D 1001	NYA
AM50005D 1001	NYA
AM50005D 5001	TEST
AM50005D 5001	NYA
AM50005D 5001	NYA
AM50005D 7001	NYA
AMCIT04D 1001	TEST
AMCIT04D 1001	NYA

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

</tbody>

Sheet1​

As you can see below I have highlighted in yellow what needs to be copied as the data in C matches and in column AC they say NYA.

Excel 2010
	C	AC
1	CatCode	StockCode
2	AAROC18 3001	NYA
3	AAROC18 3001	NYA
4	AAROC22D 3001	NYA
5	AAROC22D 3001	NYA
6	AM40004D 1001	NYA
7	AM40004D 1001	NYA
8	AM40004D 1001	NYA
9	AM50005 1001	NYA
10	AM50005 7001	TEST
11	AM50005 7001	NYA
12	AM50005D 1001	NYA
13	AM50005D 1001	NYA
14	AM50005D 5001	TEST
15	AM50005D 5001	NYA
16	AM50005D 5001	NYA
17	AM50005D 7001	NYA
18	AMCIT04D 1001	TEST
19	AMCIT04D 1001	NYA
Sheet2

 

[Dazzawm]

Thanks Leith that did it, although I had to change the ranges from sheet 2 to 1 and A2 to C2. Thanks again and also Worf for your interest.

 

[Dazzawm]

Hello Dazzawm,,

This macro highlights the entire row.

Sub TestMacro2()

    Dim Cell    As Range
    Dim col     As Long
    Dim Dict    As Object
    Dim Key     As String
    Dim Item    As Variant
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Sheet2")
        
        Set Rng = Wks.Range("A2")
        
      ' Offset from Rng column to validation column.
        col = 26
        
        EndRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
        If EndRow > Rng.Row Then Set Rng = Rng.Resize(EndRow - Rng.Row + 1, 1)
        
        Set Dict = CreateObject("Scripting.Dictionary")
        Dict.CompareMode = vbTextComapre
        
        Application.ScreenUpdating = False
        
            For Each Cell In Rng
                Key = Trim(Cell)
                If Key <> "" And Cell.Offset(0, col) = "NYA" Then
                    If Not Dict.exists(Key) Then
                        Set Item = Cell
                        Dict.Add Key, Item
                    Else
                        Set Item = Dict(Key)
                            Item.EntireRow.Interior.Color = vbYellow
                            Cell.EntireRow.Interior.Color = vbYellow
                        Set Dict(Key) = Cell
                    End If
                End If
            Next Cell
            
        Application.ScreenUpdating = True
        
End Sub

Actually Leith this didn't work correctly. If there was a group of 4 for example it highlighted the 3 rows where NYA was in AC but left the 4th row clear where there was other data. These 4 rows should have been left clear as all 4 cells in AC were not 'NYA'. Please see below.

Excel 2010
	C	AC
1725	AUA420 3032B	Test
1726	AUA420 3032B	NYA
1727	AUA420 3032B	NYA
1728	AUA420 3032B	NYA
1729	AUA420 3036A	Test
1730	AUA420 3036A	NYA
1731	AUA420 3036A	NYA
1732	AUA420 3036A	NYA
1733	AUA420 3036B	Test
1734	AUA420 3036B	NYA
1735	AUA420 3036B	NYA
1736	AUA420 3036B	NYA
Sheet1

As you can see 4 cells match in column C but not all 4 cells in column AC say 'NYA' So all these must be left clear.

 

[Dazzawm]

Can I add an extra problem please?! If the last character in column C is a letter like above then all these need to be ignored.

 

[Dazzawm]

Don't go to any further trouble Leith, it's all sorted. Thanks everyone.