copy data from a sheet but i have a duplicate data.

maybe so

Sub ttt()
Dim x, i&
'Sheet1 is ActiveSheet
i = Cells(Rows.Count, 1).End(xlUp).Row
x = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(a2:a" & i & ",0,0,ROW(1:" & i - 1 & _
                    ")),a2:a" & i & ")=1,a2:a" & i & ",CHAR(126)))"), "~", 0)
Sheets("Sheet2").Range("B2").Resize(UBound(x) + 1).Value = WorksheetFunction.Transpose(x)
End Sub

Here The Files Sir. i Try here but i have a probelem

here's your file, please try again

what the code i need to change if i transfer the vendors data in Cell C ?? and the result for sheet 2 will transfer in B6 ??

Sub asSNB()':)
Dim x, i&
'Sheet1 is ActiveSheet
i = Cells(Rows.Count, 3).End(xlUp).Row 'data in Cell C
x = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(c2:c" & i & ",0,0,ROW(1:" & i - 1 & _
                    ")),c2:c" & i & ")=1,c2:c" & i & ",CHAR(126)))"), "~", 0)
Sheets("Sheet2").Range("B6").Resize(UBound(x) + 1).Value = WorksheetFunction.Transpose(x) 'result for sheet 2 will transfer in B6
End Sub

Wow Its Working Thankyou somuch nilem. GODBLESS you and This Forum.

sorry for other question. what about the sheet, what if the vendors data is in sheet 5 how to change it ??

Sub OnceAgain()
Dim x, i&
sheets("Sheet5").activate
'Sheet5 is ActiveSheet
i = Cells(Rows.Count, 3).End(xlUp).Row 'data in Cell C
...

Thanks you so much nilem GOD Bless

Sorry again for another question.
what if i want in sheet5 get the vendors name C2 to c6000 ??

You've been asked this question
See. post #8

i try this but i have an error

i = Cells(Rows.Count, 6).End(xlUp).Row 'data in Cell C
x = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(a6:a5000" & i & ",0,0,ROW(1:" & i - 1 & _
                    ")),a6:a5000" & i & ")=1,a6:a5000" & i & ",CHAR(126)))"), "~", 0)

The Sheet5 ?
my question sir is like for example in sheet 1 i have A1 to A500 list of data and they Have a duplicate.
after that i input a data in A1500 when i run the macro. the result in sheet i not read the data i input in A1500.

Sorry if i write a wrong grammar sir. GODBLESS