Creating a list of names
I have and Excel document with several individual worksheets. On a seperate
sheet I would like to create a list of each name that appears on the spreadsheets. Some of the names appear several times but I do not want any name listed more than once on the new sheet. I would like each name to have it's own cell and don't want to have to go back through the sheets to capture every single name. Any ideas how this can be done? Any help would be appreciated. |
Creating a list of names
One way:
Check out http://www.contextures.com/xladvfilter01.html#ExtractWs Extract unique values to your summary sheet, then filter unique values if there are duplicates between sheets. In article , The BriGuy wrote: I have and Excel document with several individual worksheets. On a seperate sheet I would like to create a list of each name that appears on the spreadsheets. Some of the names appear several times but I do not want any name listed more than once on the new sheet. I would like each name to have it's own cell and don't want to have to go back through the sheets to capture every single name. Any ideas how this can be done? Any help would be appreciated. |
Creating a list of names
Here is some code to do it. Select the column with the names in it and run
the macro. It will create a new sheet with the unique names in it... 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 "The BriGuy" wrote: I have and Excel document with several individual worksheets. On a seperate sheet I would like to create a list of each name that appears on the spreadsheets. Some of the names appear several times but I do not want any name listed more than once on the new sheet. I would like each name to have it's own cell and don't want to have to go back through the sheets to capture every single name. Any ideas how this can be done? Any help would be appreciated. |
All times are GMT +1. The time now is 01:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com