building a list in a col...based on another col

  • Thread starter Thread starter KimberlyC
  • Start date Start date
K

KimberlyC

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
 
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.
 
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]
 
Back
Top