Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-10-2018, 02:27 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 106
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Loop through cells and return only unique values

Morning,


I need a bit of code to check a range of cells and only return unique values, they contain names.
Reply With Quote
  #2  
Old 10-11-2018, 06:30 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Here's a bit of code that you should be able to adapt
Code:
Sub Uniques_From_Column()
    Dim lr As Long, i As Long
    Dim dic As Object
    Dim arr As Variant
    
With Sheets("Sheet1")
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    arr = .Range("B2:B" & lr)
End With

Set dic = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(arr, 1)
  dic(arr(i, 1)) = 1
Next i

'write uniques to a column
Range("E2").Resize(dic.Count) = Application.Transpose(dic.keys)
' or
'do something with each unique
For i = 0 To dic.Count - 1
    'do what you want with each
    'this prints to the immediate window
    Debug.Print dic.keys()(i)
Next i

End Sub

Excel VBA Dictionary – A Complete Guide

Last edited by NoSparks; 10-11-2018 at 09:42 AM. Reason: add link
Reply With Quote
  #3  
Old 10-11-2018, 11:34 AM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 106
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Thanks, but after looking on the endless web I put together this.
Code:
   For Each rcell In Range("L4:L150")
        If rcell.Value Like "?*@?*.?*" Then
           next_cell_value = next_cell_value & "; " & rcell
           If InStr(next_cell_value, rcell) = 0 Then next_cell_value = next_cell_value & rcell & ";"
        End If
   Next rcell
Reply With Quote
  #4  
Old 10-11-2018, 01:48 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

You might want to double check that.
Reply With Quote
  #5  
Old 10-11-2018, 03:49 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 106
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

I see my error, I'm trying to work it out now.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to group table by unique values in PowerPivot rlf793 Excel 0 05-17-2017 05:23 PM
Count Duplicate Values without a specific Unique Value Brittni Excel 1 02-01-2017 06:22 PM
Count unique values that match 2 or more criteria caeiro01 Excel 1 10-25-2015 02:34 AM
Combining Records of two sheets with Loop till Unique ID abhilashv Mail Merge 1 01-22-2014 04:02 AM
Display unique values and count the number of child items vthomeschoolmom Excel 2 07-25-2013 06:17 AM


All times are GMT -7. The time now is 06:46 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft