Getting unique values from a column

Question

The task at hand is to search in column A to see what values I have (they are in form of letters) and paste for each unique entry, its value once in another column.

Here is a visual explanation:

What I came up with was to create a For loop that iritiates through column A and created a conditional that if it found a certain value then it would insert the value in the range. Here is the code:

For i = 1 to 26

if cells(i,26).value= "A" Then

Range ("C1")= "A"

Elseif cells(i,26).value = "B" then
Range ("C2").value = "B"
ElseIf (i,26).value = "C" then 
Range ("C3").value = "C"
EndIf
Next i
end sub 

I want to cut this process short as my data set is really big with lots of company names. Any recommendations? I believe there has to be a way of knowing the values without having to look at all the values yourself.

Answer 1

If the goal is to just get a unique list of values found in Column A output to Column C you can use the below macro. This is really just recreating the steps of one method you would manually take to find unique values. Not the most sophisticated solution, but it works

1. Create a copy of your column with company names (using last available column in sheet)
2. De-dup the helper column
3. Copy the de-duped column to destination
4. Delete the helper column

Assumes the last column on worksheet is not used

---

Sub Unique()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lr As Long, lc As Long

'Determine Range Size
lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
lc = ws.Cells(1, ws.Columns.Count).Column

'Copy Company Names To Helper Column/Remove Duplicates
ws.Range("A2:A" & lr).Copy ws.Cells(1, lc)
ws.Columns(lc).RemoveDuplicates Columns:=1, Header:=xlNo
lr = ws.Cells(ws.Rows.Count, lc).End(xlUp).Row

'Output Unique Values From Helper Column
ws.Range(ws.Cells(1, lc), ws.Cells(lr, lc)).Copy
ws.Range("C2").PasteSpecial xlPasteValues

'Delete Helper Column
ws.Columns(lc).Delete

End Sub

---

Note my comment on post. VBA may not be needed here at all