Selecting data from one column

Gawfer

New Member
Joined
Apr 28, 2002
Messages
2
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??
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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?
 
Upvote 0
Q1 Follow up

One of two ways to try

Code:
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

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top