For Each Unique Value ... Next

[ryancgarrett]

I have a list around 1,000 cells long in column A with about 100 unique values. I need to set up a For ... Next loop but only want the loop to run once per unique value. I've thought of copying the values to a temporary column and removing duplicates, then running the loop and deleting the column but it seems there should be an easier way.

 

[JoeMo]

I have a list around 1,000 cells long in column A with about 100 unique values. I need to set up a For ... Next loop but only want the loop to run once per unique value. I've thought of copying the values to a temporary column and removing duplicates, then running the loop and deleting the column but it seems there should be an easier way.

Not sure I understand what you want to do this for, but this will isolate the unique cells and allow you to do something with them.

Sub ForEachUnique()
Dim lR As Long, R As Range, c As Range, n As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1", "A" & lR)
For Each c In R
    If Application.CountIf(R, c.Value) = 1 Then
        'do something
    End If
Next c
End Sub

 

[ryancgarrett]

Thanks JoeMo, I can definitely use that code. What is the "n" variable used for?

 

[JoeMo]

Thanks JoeMo, I can definitely use that code. What is the "n" variable used for?

You can remove that- it's not used and just an oversight that I forgot to remove it.

 

[JoeMo]

You can remove that- it's not used and just an oversight that I forgot to remove it.

Well, there was a reason I put that n in the dim statements, just forgot to use it. The result is the code I gave you will isolate cells with values that appear just once in your list, but that's not what you asked for. You want to isolate all unique values once each. This revision that includes the n will do that:

Sub ForEachUnique()
Dim lR As Long, R As Range, c As Range, n As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1", "A" & lR)
For Each c In R
    n = n + 1
    If Application.CountIf(Range(R.Cells(1, 1), R.Cells(n, 1)), c.Value) = 1 Then
        'do something
    End If
Next c
End Sub

 

[ryancgarrett]

Thanks for the update! I was thinking about it last night and realized that since my list is already sorted descending, would my code run faster if it were simpler and looked something like:

For Each cell In Range
    If cell.Value <> cell.Offset(0, -1).Value Then
        'do something
    End If
Next cell

Just thinking about the different options and speed.

 

[JoeMo]

Thanks for the update! I was thinking about it last night and realized that since my list is already sorted descending, would my code run faster if it were simpler and looked something like:

For Each cell In Range
    If cell.Value <> cell.Offset(0, -1).Value Then
        'do something
    End If
Next cell

Just thinking about the different options and speed.

Anyway you slice it, the For Each - Next procedure is very slow because each cell is checked. If you have many cells, it is much faster to put their contents in memory and process the elements in memory. If you want to explain how your data are laid out and what you want to accomplish, maybe there's a faster alternative.

 

[ryancgarrett]

I am trying to automate our customer statement process. Eventually I'll design something a lot cleaner but this is kind of a "duct tape and bailing wire" solution for the next month or two while I design a better application to handle the process.

From our accounting software we export a list of all of the transactions that occurred during the month (typically around 1,000 transaction for about 100 customers) which has the customer number in the far left column. On a separate sheet I have a filter set up in such a way that when a customer number is selected from a drop down it spits out the "guts" of their customer statement which is then copied and pasted into a statement template for that customer.

With the "quick fix" I'm trying to have the loop find each unique customer number, filter the second sheet for that value, and then paste the resulting information into a new blank customer statement sheet. Can you think of a more efficient way than a For...Next loop to accomplish this? The spreadsheet has confidential information but I can PM you if it would be easier to see.

 

[JoeMo]

There are a number of ways to get a unique list from a longer list containing duplicates, including remove duplicates, advanced filter, a worksheet formula, a scripting dictionary and a collection that would be faster than the For Each - Next cell approach.