Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Selecting data from one column

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •