pull unique values from a column as they appear

[jonattempt_cell]

I am trying to write a vba function, sub or to call a sub / function that would pull unique values in a column's range to another, larger column on the same or different worksheet. I want to pull the unique values as they appear because they may disappear.

Imagine for example people enter a restaurant and orders arrive to the chef but are thrown i nthe bin once they leave. Each order has a unique receipt and I'd like that receipt to be recorded and entered in the last empty row of the destination column.

For example column B3:B30 contains the receipt id for customers as they arrive. I'd need column C:C to record to the last empty cell the unique orders as they arrive. As people leave the restaurant column B3:B30 will fill to the top so the code will need to adjust and search the whole of B3:B30

so far I have

<code>Sub ertdfgcvb()
Dim rng As Range
Dim Unique As Boolean

For Each rng In Worksheets("Sheet1").Range("B1:B30") 'for each cell in your B1 to B30 range, sheet1
    Unique = True 'we'll assume it's unique
    Lastunique = Worksheets("Sheet2").Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To Lastunique 'for each cell in the unique ID cache
        If rng.Value = Worksheets("Sheet2").Cells(i, 2).Value Then 'we check if it is equal
            Unique = False 'if yes, it is not unique
        End If
    Next
    If Unique Then Worksheets("Sheet2").Cells(Lastunique + 1, 2) = rng 'adds if it is unique
Next
End Sub

</code>
<code><code>

Private Sub WorkSheet_Change(ByVal Target As Range)
Call ertdfgcvb
End Sub

</code>

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim rng As Range
most = Now
For Each rng In Target
    If rng.Column = 2 Then 'if it's in B column
        Unique = True 'we'll assume it's unique
        Lastunique = Worksheets("Sheet2").Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For i = 1 To Lastunique 'for each cell in the unique ID cache
            If rng.Value = Worksheets("Sheet2").Cells(i, 2).Value Then 'we check if it is equal
                Unique = False 'if yes, it is not unique
            End If
        Next
        If Unique Then Worksheets("Sheet2").Cells(Lastunique + 1, 2) = rng 'adds if it is unique
    End If
Next
MsgBox (Format(Now - most, "h:mm:ss"))
End Sub</code>

but none seem to work

 

[jonattempt_cell]

as for how column D's values changes that happens from another microsoft application. Nonetheless the values get filled

I am looking at this http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula

I have no idea

 

[MickG]

I'm not sure I can help you, but if you could send an example file (Using "Box.com", free file sharing) with your Data/formulas and expected results , I'll have a look.

 

[jonattempt_cell]

I'm not sure I can help you, but if you could send an example file (Using "Box.com", free file sharing) with your Data/formulas and expected results , I'll have a look.

It won't work we'd have to screenshare because the application I am using is licensed to my computer and you would need to install a trading appliaction aswell. MT4. You could try it on a free trial