greetings! if i have a range say 40 columns by 10 rows with values in half
of the cells, is there a formula, or macro, or best method, where i can
determine what values are in the range. note, there are many values which
will show up multiple times in the range. i was hoping to use a pivot table
but i'm not sure how to use one over a 40 by 10 range.

What did you want to know... Are you looking for a listing of the unique
entries or ???
--
HTH...

Jim Thomlinson


"mwam423" wrote:

> greetings! if i have a range say 40 columns by 10 rows with values in half
> of the cells, is there a formula, or macro, or best method, where i can
> determine what values are in the range. note, there are many values which
> will show up multiple times in the range. i was hoping to use a pivot table
> but i'm not sure how to use one over a 40 by 10 range.

hi jim, yes, that's exactly what i want, listing of unique values

"Jim Thomlinson" wrote:

> What did you want to know... Are you looking for a listing of the unique
> entries or ???

Here is some code... It looks at the currently selected cells and creates a
new sheet that contains only the unique items frm that selection...

Public Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Object 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

Application.ScreenUpdating = False
'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = CreateObject("Scripting.Dictionary")

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value <> Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"mwam423" wrote:

> hi jim, yes, that's exactly what i want, listing of unique values
>
> "Jim Thomlinson" wrote:
>
> > What did you want to know... Are you looking for a listing of the unique
> > entries or ???
>

hi jim, many, many thanks for that code, works like a charm =D and really
appreciate the annotations. couple questions, there is line:

If Not dic.Exists(cell.Value) And cell.Value <> Empty Then

is there a glossary somewhere that defines terms like "exists"?

also, rngPaste.NumberFormat = "@" 'Format cell as text

is there reason values are formatteed as text? is there any difference if
values were formatted as numbers?

thanks again, and have a great weekend!



"Jim Thomlinson" wrote:

> Here is some code... It looks at the currently selected cells and creates a
> new sheet that contains only the unique items frm that selection...
>
> Public Sub GetUniqueItems()
> Dim cell As Range 'Current cell in range to check
> Dim rngToSearch As Range 'Cells to be searched
> Dim dic As Object 'Dictionary Object
> Dim dicItem As Variant 'Items within dictionary object
> Dim wks As Worksheet 'Worksheet to populate with
> unique items
> Dim rngPaste As Range 'Cells where unique items are
> placed
>
> Application.ScreenUpdating = False
> 'Create range to be searched
> Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
> If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell
>
> 'Confirm there is a relevant range selected
> If Not rngToSearch Is Nothing Then
> 'Create dictionay object
> Set dic = CreateObject("Scripting.Dictionary")
>
> 'Populate dictionary object with unique items (use key to define
> unique)
> For Each cell In rngToSearch 'Traverse selected range
> If Not dic.Exists(cell.Value) And cell.Value <> Empty Then
> 'Check the key
> dic.Add cell.Value, cell.Value 'Add the item if unique
> End If
> Next
>
> If Not dic Is Nothing Then 'Check for dictionary
> Set wks = Worksheets.Add 'Create worksheet to populate
> Set rngPaste = wks.Range("A1") 'Create range to populate
> For Each dicItem In dic.Items 'Loop through dictionary
> rngPaste.NumberFormat = "@" 'Format cell as text
> rngPaste.Value = dicItem 'Add items to new sheet
> Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
> Next dicItem
> 'Clean up objects
> Set wks = Nothing
> Set rngPaste = Nothing
> Set dic = Nothing
> End If
> End If
> Application.ScreenUpdating = True
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "mwam423" wrote:
>
> > hi jim, yes, that's exactly what i want, listing of unique values
> >
> > "Jim Thomlinson" wrote:
> >
> > > What did you want to know... Are you looking for a listing of the unique
> > > entries or ???
> >