Selecting Unique Trade Signals From A Large List

[profitscore]

I use excel to write algorithms that produce trades signals (Long,Short,Cash). These signals are generated day by day and the data can go back years, so the list are very large. Most days have repeating signals so the signal will be the same signal day after day.

Column A Column B
Date Signal
1/5/2011 Long
1/6/2011 Long
1/7/2011 Long
1/8/2011 Cash
1/9/2011 Cash
1/10/2011 Short
1/12/2011 Short
etc

From time to time, I need to take the entire list and sort out only unique signals and remove the repeating signals. Today I do this in 4 steps:

1.) I Write a simple If function in column C that identifies unique signals and places a 1 every time this occurs.
2,) Sort the table by column C
3.) Delete repeating signals from the sorted list
4.) Resort the remaining unique signals by date to achieve my objective.

This takes several error prone steps and I am looking for a simpler way to do this.

What I would like to do is go to the bottom of the large list and write some clever Excel function that looks at the list above and only grabs unique signal change dates and signal types from the list above and produces the following list of unique signals minus all repeating signals in the order in which they occur.

Column A Column B
Date Signal
1/5/2011 Long
1/8/2011 Cash
1/10/2011 Short

There is only one problem. I am not clever enough to write the function. Can one of you Excel gurus out there tell me how to do this?

 

[Norie]

Not sure about a function but if you wanted code then perhaps something like this.

Dim rng As Range
    Set rng = Range("B2")
    
    While rng.Value <> ""
    
        If rng.Value = rng.Offset(1).Value Then
            rng.Offset(1).EntireRow.Delete xlShiftUp
            
        Else
            Set rng = rng.Offset(1)
        End If
    Wend

Note this is only very lightly tested, it works with your sample data and some other simple data sets.