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.