I have a list of values that are not in order yet some repeat. I would like to create a column next to my current column of values that has each unique value from the orginal column. For example, say Column A has 20 values in Rows 1 to 20 and there are 7 unique values in Column A. I would like Column B to list the seven unique values within Column A in Rows 1 to 7 in the order they appear in Column A.
New Column With Unique Values From Adjacent Column
-
-
-
Re: New Column With Unique Values From Adjacent Column
Code
Display MoreSub test() Dim a, e, b(), n As Long a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value Columns("b").ClearContents If Not IsArray(a) Then Range("b1").Value = a : Exit Sub End If ReDim b(1 To UBound(a,1), 1 To 1) With CreateObject("Scripting.Dictionnary") .CompareMode = vbTextCompare For Each e In a If Not IsEmpty(e) And not .exists(e) Then n = n + 1 : b(n,1) = e .add e, Nothing End If Next End With Range("b1").Resize(n).Value = b End Sub
-
Re: New Column With Unique Values From Adjacent Column
You can use Advanced Filter, select Unique items & Copy to another location.
-
Re: New Column With Unique Values From Adjacent Column
royUK and jindon,
Thank you for the responses.
First I did not quite specify exactly how I wanted to perform the intended task. First I want the task to be automated. So based on input values it looks through the list of values and determines the first instance of each non-repeating value. The next step is it takes the first instance of each non-repeating value and copies to a new cell in an adjacent column.
I would like to perform this process without the use of code and only using formulas, if that is possible. If it is not possible without code, how exactly to I get the code to execute when a cell value (input cell) is changed within a set of cells. Note the input cells are not adjacent to one another.
Any further assistance is much appreciated.
Jason
-
Re: New Column With Unique Values From Adjacent Column
Roy's answer is the way to go....DATA>>>FILTER>>>ADVANCED FILTER...all fairly self explanatory after that and you'll end up with a unique list
don't think you will be able to automate it without code though,,,you could have code that would refresh the exising unique list everytime a cell within the range changes...
-
-
Re: New Column With Unique Values From Adjacent Column
Quote from jindon;346060Code
Display MoreSub test() Dim a, e, b(), n As Long a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value Columns("b").ClearContents If Not IsArray(a) Then Range("b1").Value = a : Exit Sub End If ReDim b(1 To UBound(a,1), 1 To 1) With CreateObject("Scripting.Dictionnary") .CompareMode = vbTextCompare For Each e In a If Not IsEmpty(e) And not .exists(e) Then n = n + 1 : b(n,1) = e .add e, Nothing End If Next End With Range("b1").Resize(n).Value = b End Sub
Great code works well, just needs a 'n' removing from 'Dictionnary' to work as below:)
Code
Display MoreSub test() Dim a, e, b(), n As Long a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value Columns("b").ClearContents If Not IsArray(a) Then Range("b1").Value = a : Exit Sub End If ReDim b(1 To UBound(a,1), 1 To 1) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare For Each e In a If Not IsEmpty(e) And not .exists(e) Then n = n + 1 : b(n,1) = e .add e, Nothing End If Next End With Range("b1").Resize(n).Value = b End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!