Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
detemining values in a range
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
detemining values in a 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
detemining values in a 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 ??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
detemining values in a range
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 ??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
detemining values in a range
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 ??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Max values from 2 ws and a range | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Last 2 values in range | Excel Discussion (Misc queries) |