Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-10-2018, 02:27 PM
trevorc trevorc is offline Loop through cells and return only unique values Windows 7 32bit Loop through cells and return only unique values Office 2013
Competent Performer
Loop through cells and return only unique values
 
Join Date: Jan 2017
Posts: 174
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 Loop through cells and return only unique values Windows 7 64bit Loop through cells and return only unique values Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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 Loop through cells and return only unique values Windows 7 32bit Loop through cells and return only unique values Office 2013
Competent Performer
Loop through cells and return only unique values
 
Join Date: Jan 2017
Posts: 174
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 Loop through cells and return only unique values Windows 7 64bit Loop through cells and return only unique values Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

You might want to double check that.
Reply With Quote
  #5  
Old 10-11-2018, 03:49 PM
trevorc trevorc is offline Loop through cells and return only unique values Windows 7 32bit Loop through cells and return only unique values Office 2013
Competent Performer
Loop through cells and return only unique values
 
Join Date: Jan 2017
Posts: 174
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
Loop through cells and return only unique values Count unique values that match 2 or more criteria caeiro01 Excel 1 10-25-2015 02:34 AM
Loop through cells and return only unique values Combining Records of two sheets with Loop till Unique ID abhilashv Mail Merge 1 01-22-2014 04:02 AM
Loop through cells and return only unique values Display unique values and count the number of child items vthomeschoolmom Excel 2 07-25-2013 06:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft