How to exclude requested values?

[Ofirabo]

Hello,

I have many "text values" in one column, i want to exclude the values that appears just once and display them in a differnet column.
what function should i use and how?
for example:
apple
apple
apple
orange
tomato
tomato

Thanks
Ofira

 

[Domenic]

Assuming that Column A contains the text values, the following macro will list in Column C values that occur more than once...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    NextRow = 1
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow
        [color=darkblue]If[/color] WorksheetFunction.CountIf(Range(Cells(1, "A"), Cells(LastRow, "A")), Cells(i, "A").Value) > 1 [color=darkblue]Then[/color]
            Cells(NextRow, "C").Value = Cells(i, "A").Value
            NextRow = [color=darkblue]Next[/color]Row + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    Next i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

If you in fact want a list of text values than occur only once, try replacing...

If WorksheetFunction.CountIf(Range(Cells(1, "A"), Cells(LastRow, "A")), Cells(i, "A").Value) > 1 Then

with

If WorksheetFunction.CountIf(Range(Cells(1, "A"), Cells(LastRow, "A")), Cells(i, "A").Value) = 1 Then