Returning result of Sheet1.ColumnB to Sheet2.ColumnB given a certain value exists in any cell for Sheet1.ColumnC

So I have a problem. I have something akin to this in Sheet1, where I have multiple instances of the same term with different (or sometimes non-existent) definitions:

     Term Name    Definition      Chosen Definition?           
     Account      The account.      
     Account      
     Account      An account name.        x

Sheet2 is a reference sheet with all of the unique term names, but no definitions as of yet.

    Term Name     Definition
    Account
    Product
    Customer

Since I have so many duplicates of the same term in Sheet1, I am trying to write something where I can simply put an 'x' in the third column ('Chosen Definition?') to mark the definition that I have chosen, and it will take the adjacent definition cell from Sheet1 (in Column B), and put it into the right spot in Sheet2 like so:

    Term Name     Definition
    Account       An account name.
    Product
    Customer

I assume this would be done either by combining something like an IF statement, an AND statement, and a VLOOKUP, or in VBA (which I'm not an expert in at all). I've tried both methods and I feel like I've gotten somewhat close, but not quite there. Here's my VBA code:

    Sub InputData()
        Dim c as Range
        Dim j as Integer
        Dim Source as Worksheet
        Dim Target as Worksheet

        Set Source = ActiveWorkbook.Worksheets("Sheet1")
        Set Target = ActiveWorkbook.Worksheets("Sheet2")

        j = 2
        For Each c in Source.Range("C3:C1000")
            If c = "x"
                Source.Rows(c.Rows).Copy Target.Rows(j)
                j = j + 1
            End If
        Next c
    End Sub

That just puts any row with an 'x' in column C at the beginning of Sheet2:

    Term Name     Definition
    Account       An account name.
    Account
    Product
    Customer

Am I getting close with the VBA code that I have? I don't think that formulas will work since I believe that what I am looking for needs to loop through each cell to find a match, but I don't want to add a new row each time I find a match.

1 answer

  • answered 2018-11-07 23:20 QHarr

    You can use a formula. For example, in C2 sheet2, with your data as shown in A1:C4 sheet1

    =INDEX(Sheet1!$B$2:$B$4,MATCH(A2&"x",Sheet1!$A$2:$A$4&Sheet1!$C$2:$C$4,0))
    

    Enter as an array formula with Ctrl +Shift+Enter

    enter image description here

    You could wrap in an IFERROR(formula,"") to mask any non matches.