Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a list in a col...based on another col
Hi
I have a worksheet where users enter codes next to employees name that represents the dept they work in. The codes are entered into col C beginning in cell 9 and ending in cell 64. I would like to have a list build automatically in col F begining in cell 9 that recaps the codes entered in column C...but not repeat any codes once they are entered.. For example; Col C might look like this 865 492 965 865 865 492 965 210 592 and so on... Based on the data above.. I would like Col F (cells 9 thru 64 is possible) to display the dept codes once it's entered in col C.(but not repreated if entered again) For example: Col F (beginning in cell 9 thru 13) would look like this...based on Col C above: 865 492 965 210 592 I'm not sure how this can be done.. Any help would be greatly appreciated... Thanks in advance Kimberly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a list in a col...based on another col
select the values in column C, then do data=Filter=Advanced filter, select
unique check box and copy to another location. Leave criteria blank and select F9. -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Hi I have a worksheet where users enter codes next to employees name that represents the dept they work in. The codes are entered into col C beginning in cell 9 and ending in cell 64. I would like to have a list build automatically in col F begining in cell 9 that recaps the codes entered in column C...but not repeat any codes once they are entered.. For example; Col C might look like this 865 492 965 865 865 492 965 210 592 and so on... Based on the data above.. I would like Col F (cells 9 thru 64 is possible) to display the dept codes once it's entered in col C.(but not repreated if entered again) For example: Col F (beginning in cell 9 thru 13) would look like this...based on Col C above: 865 492 965 210 592 I'm not sure how this can be done.. Any help would be greatly appreciated... Thanks in advance Kimberly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a list in a col...based on another col
Hi Kimberly,
If you want this to update automatically as people enter new codes, then you can automate the Advanced Filter. The following code should be placed behind the worksheet (right-click sheet tab, view code): Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, _ Range("C9:C64")) Is Nothing Then Range("F8:F64").ClearContents gCopyUnique Range("C8:C64"), Range("F8") End If End Sub The following code should be placed in a standard code module: Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range) rrngSource.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=rrngDest, Unique:=True End Sub -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] KimberlyC wrote: Hi I have a worksheet where users enter codes next to employees name that represents the dept they work in. The codes are entered into col C beginning in cell 9 and ending in cell 64. I would like to have a list build automatically in col F begining in cell 9 that recaps the codes entered in column C...but not repeat any codes once they are entered.. For example; Col C might look like this 865 492 965 865 865 492 965 210 592 and so on... Based on the data above.. I would like Col F (cells 9 thru 64 is possible) to display the dept codes once it's entered in col C.(but not repreated if entered again) For example: Col F (beginning in cell 9 thru 13) would look like this...based on Col C above: 865 492 965 210 592 I'm not sure how this can be done.. Any help would be greatly appreciated... Thanks in advance Kimberly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
building list | Excel Worksheet Functions | |||
Building list based on condition | Excel Worksheet Functions | |||
self-building dropdown list | Excel Worksheet Functions | |||
Building a String based on Selected Check boxes | Excel Programming |