Selecting data from one column

[Gawfer]

Two questions, if I may.
(1) I am entering data into column B. Some of the data repeats. As I enter the data, I want each entry to also appear in column C, but I do not want duplicates in column C. What do I need to do?

(2) I want to enter a numeric value (ie 101) and have a text value appear in the cell. I have a number of these "codes" that I need to work with, so I assume I'll need a list of some type??

[Jay Petrulis]

Hi,

Q1: Use the worksheet_change event.

In the module for the sheet, copy and paste the following

'----------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub
If Len(Target) = 0 Then
Target.Offset(0, 1).ClearContents
Exit Sub
End If
If WorksheetFunction.CountIf(Range("B:B"), Target.Value) = 1 Then Target.Offset(0, 1) = Target

End Sub
'-------------

Q2: Same idea, but we'll have to create a lookup array for you. Please post your details.

Bye,
Jay

[Gawfer]

Jay,
Thanks for your help.

A follow up to Q1. Is there anything I can add to the code to have the data in Col C begin in row 7 and fill in consecutive rows rather than in the row it appears in in Col B?

Details on Q2. We have employee numbers such as 1 for PWC, 2 for SGG, 3 for JFD .....
Is there some way to enter the code number into Col A, and have the initials for the employee show up in place of the number?

[Jay Petrulis]

Q1 Follow up

One of two ways to try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long

If Target.Column = 2 Then
    If WorksheetFunction.CountIf(Range("B:B"), Target.Value) = 1 Then
        lastrow = WorksheetFunction.Max(6, Cells(Rows.Count, 3).End(xlUp).Row)
        Cells(lastrow + 1, 3) = Target
    End If
End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub
If Len(Target) = 0 Then
    Target.Offset(6, 1).ClearContents
    Exit Sub
End If
If WorksheetFunction.CountIf(Range("B:B"), Target.Value) = 1 Then Target.Offset(6, 1) = Target

End Sub

Will post response to Q2 when I figure it out.

Bye,
Jay

[Jay Petrulis]

Q2:
The routine conflicts with the others given, as col A changes fill column B, but nothing is triggered in B. So if these are to be used in conjunction the routines must be changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyArr

MyArr = Array("ABC", "BCD", "CDE", "DEF", "EFG", "FGH", "GHI")

If Target.Column = 1 Then Target.Offset(0, 1) = MyArr(Target - 1)

End Sub

[ This Message was edited by: Jay Petrulis on 2002-05-02 17:12 ]