excel vba: copy rows if data match with values in column in another sheet

Question

I have ask a related question here.
Sir Adelaide has provided me this very useful solution.

So now, in this almost similar case, I have 2 excel sheets in my workbook.
[Xsheet][1] Sheet1

I'm gonna loop through the name and description column in Sheet1 to see if it match the value in name or description column in XSheet (there might be infinite data rows in the column). If they do, then 'that' row in Sheet1 would be copied into new Sheet2.

I have modified a little bit in the previous coding (provided by Sir Adelaide),

Sub Procedure2()

Dim xsht As Worksheet
Dim sht As Worksheet 'original sheet
Dim newsht As Worksheet 'sheet with new data

Set xsht = ThisWorkbook.Worksheets("Xsheet")
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")

'Set dat = sht.Range("code").Cells(1,1)
Set main = xsht.Range("A1")
Set dat = sht.Range("A1")
Set newdat = newsht.Range("A1")

'initialise counters
i = 1
j = 1

'set heading on sheet2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status

Do While dat.Offset(i, 0).Value <> "" 'loop row till code data goes blank
  If ((main.Offset(i, 0).Value = dat.Offset(i, 4).Value Or _
  main.Offset(i, 1).Value = dat.Offset(i, 5).Value) And dat.Offset(i, 6).Value = "active") Then 'check conditions
    newdat.Offset(j, 0).Value = dat.Offset(i, 0).Value 'copy code
    newdat.Offset(j, 1).Value = dat.Offset(i, 2).Value 'copy title
    newdat.Offset(j, 2).Value = dat.Offset(i, 3).Value 'copy date
    newdat.Offset(j, 3).Value = dat.Offset(i, 4).Value 'copy name
    newdat.Offset(j, 4).Value = dat.Offset(i, 5).Value 'copy descr
    newdat.Offset(j, 5).Value = dat.Offset(i, 6).Value 'copy status
    j = j + 1
  End If

  i = i + 1
Loop

Any advice provided would be appreciated. Thank you.
output Hi, I've tried to run the updated code.
This is my output, but there is an inactive case, which is not correct.
The correct output should be 4566,4987,4988.
I have go through the code, Idk what went wrong

I take away Xsheet link bcause I dont have enough reputation to make more than 2 hyperlink

I now looping through the Sheet1 to see if its match the columns in Xsheet.
4566, it matches 'Adam' in name col (since it's name or description so if name matches then it's a match), and (need to be) active, so its in.
4899, Edward is a match (or whatever description), but did not match and active, so no.
4987, same case as 4566, its Adam and active.
4988, Kris (not a match name), but al is in Xsheet's description, and active, so it's in.
4989, Chris not a match name, ttr not a match description, even its an active case (I also wont take it in)

Thank you for your guidance so far. I really appreciate it.

Solution

So after finding out what you are really doing. The question is simple:

"If the Name or the Description on the Master List is found in the Data Sheet and it is also Active, then copy it to a new sheet".

Logical Operators : Order of precedence

Here is a revision of the code wrt your recent comment.

Sub Procedure2()

Dim xsht As Worksheet
Dim sht As Worksheet 'original sheet
Dim newsht As Worksheet 'sheet with new data

Set xsht = ThisWorkbook.Worksheets("Xsheet")
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")

'Set dat = sht.Range("code").Cells(1,1)
Set main = xsht.Range("A1")
Set dat = sht.Range("A1")
Set newdat = newsht.Range("A1")

'initialise counters
Dim i, j, iRow As Integer   'instantiate and initialize the integers
i = 1
j = 1
iRow = 1

'set heading on sheet2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status

Do While main.Offset(i, 0).Value <> "" Or main.Offset(i, 1).Value <> ""

  j = 1     'reset DataSheet pointer

  Do While dat.Offset(j, 0).Value <> ""

    If (main.Offset(i, 0).Value = dat.Offset(j, 4).Value _
    Or main.Offset(i, 1).Value = dat.Offset(j, 5).Value) _
    And dat.Offset(j, 6).Value = "active" Then

      newdat.Offset(iRow, 0).Value = dat.Offset(j, 0).Value 'copy code
      newdat.Offset(iRow, 1).Value = dat.Offset(j, 2).Value 'copy title
      newdat.Offset(iRow, 2).Value = dat.Offset(j, 3).Value 'copy date
      newdat.Offset(iRow, 3).Value = dat.Offset(j, 4).Value 'copy name
      newdat.Offset(iRow, 4).Value = dat.Offset(j, 5).Value 'copy descr
      newdat.Offset(iRow, 5).Value = dat.Offset(j, 6).Value 'copy status
      iRow = iRow + 1
    End If
    j = j + 1     'increment DataSheet pointer; fast moving; changing/resetting
  Loop

  i = i + 1     'increment XSheet pointer; slow moving outer loop; not resetting
Loop
End Sub

This revised code has FOUR changes. Added the check in the OUTER Loop to include blanks in Name field by adding Or main.Offset(i, 1).Value <> "". The change of where the information was being evaluated from i-to-i_value, to i-to-j_value, in the If statement. The addition of a third counter for data placement in the new sheet for copied data to Sheet2. And lastly, a nested loop (loop inside a loop). Loop-Outer: Looks at the Master List (xSheet) row-by-row; never repeats. Loop-Inner: Looks at the data sheet to compare top-to-bottom; repeats every new row in Master List.

You could even change the If statement to consider "active" vs. "Active", or "A" or "a". This is where a drop list comes in handy, but that's another problem in itself.

If (main.Offset(i, 0).Value = dat.Offset(j, 4).Value _
Or main.Offset(i, 1).Value = dat.Offset(j, 5).Value) _
And (dat.Offset(j, 6).Value = "active" Or dat.Offset(j, 6).Value = "Active") Then

Comments

[Admin]

It would help to post screen shots of your Sheets and also paste your code in its entirety in a block.

[Admin]

Also this would give insight to what main and data is. If main is a sheet name, then you'd have to change it to Sheets("main").range ("A1").offeet (i,j).Value

[Admin]

Hi ejbytes, I've upload the screesnshot with the code. Thanks!

[Admin]

I figured it out for you. Simply click in the faint CheckMark next to to the solution to accept the answer. Don't feel bad, the best of us miss this one all the time. Welcome to SuperUser!

[Admin]

So in plain words: If the (Name or Description matches), then ask, is it Active. If all true, then copy. Or is it just simply, "Is this active?" if so, lets copy the info over. I think that's really it right? In that case it would make more sens to go through the S-list and check to see if the Master is requesting it and if so copy it. You know this could be done in a Table and filter, no need for VB code at all.

[Admin]

Well you aren't using all data, that's why it's failing. You are checking until there are not empty cells, but you left Rows blank in column_1 in X Sheet so it never completed the search. Here is the last edit. Remember to ask the question in words in a way you would do comparing a list with left finger and another list with your right finger. Here is the change and now you are good to go. DONE!

[Admin]

Please, just click the Checkmark to it turns green and the solution is closed. Hope you learned a lot today, it was long and arduous. Like my computer science professors would always profess, "Don't just start coding you'll waste a lot of time. Define your problem first. Then solve one question at a time in a piece-wise or top-down approach", aka Divide and Conquer.

[Admin]

Hi ejbytes, thank you so much. I've learned a lot. But i just have this one question, you declare j=1 (again) before the Do while. May I know why is that the results different when I take away it? I thought it shouldnt make a diff as it's already been declared in earlier code.

[Admin]

Yes J=1 has to be declared over each time in the inner loop. Why? Put your left pointer-finger on XSheet as if it were a book with a list in it and go down the list one-at-a-time. Now, with your right-pointer-finger, use it to check the DataSheet, skimming down looking for what your left-pointer-finger is glued to at the moment. Then move the left finger down one single row to the next name. Then traverse the Data again with your right finger starting at the top and going down. Left finger moves slow, while right finger goes up and down over and over again.

[Admin]

Woo! I see how it works now! wow! Once again, thank you for your time and guidance so far ! I'm really appreciate it! :)

[ExcelNovice]

Hi ejbytes, Thank you for sharing me this information. I've replace the code. but Do While dat.Offset(i, 0).Value "" 'loop row till code data goes blank is in red due to syntax error.

[ejbytes]

If you've saved it, copy the code you posted (missing end sub), then add a single left parenthesis and a single right parenthesis. You should be fine. Your code, otherwise, was fine. Just logical error with good syntax.

[ExcelNovice]

If ((main.Offset(i, 0).Value = dat.Offset(i, 4).Value Or _ main.Offset(i, 1).Value = dat.Offset(i, 5).Value) And dat.Offset(i, 6).Value = "active") Then Hi ejbytes, I modified the code a lil bit because I want the outcome to be, if name match and active (copy), if description match and active (copy). So it should be 4566,4987,4988. But I am only getting 4566 for now. Would you shed me some light on this ? thank you.

[ejbytes]

That's correct, you'll only get 4566, it's all because of the missing parentheses.

[ejbytes]

Oh... I see what you are trying to do. I thought your code was what you were after. But your last comment just struck a cord. I'm pretty sure this is what you are after. It's a Big(O), of n^2, or the size of the list times the size of the list. There are ways to optimize this, but this will get you there for now.

[ejbytes]

There are 4 major changes. Note the While loop, is now the outer loop. Also in the If statement, letters are changed from i to j on one side of the evaluation. Another counter was added. An inner loop (nesting) was added to compare each time a row is being evaluated from XSheet. And what we already discussed; the grouping of data with parentheses in the If statement. You can copy the whole thing and replace all the code with this new code. But do note the changes. Create a new book if necessary to follow how I reconstructed the code to fit your criteria.

[ExcelNovice]

Hi ejbytes, thank you once again. I've upload my output in the question above. Would you mind take a look at it? Cause, Im still having trouble getting my desire output . Thanks Xsheet : i.stack.imgur.com/PBLR5.png

[ejbytes]

@ExcelNovice Check out my new comments above. I've also changed my solution based on your comments.

[ejbytes]

@ExcelNovice The correct output should be 4566,4987,4988. This doesn't make sense. To be true should be Name on X_name = S1_name. Or, Description_val = S1_val AND S1="Active". Right? That's what your statement indicates.

[ejbytes]

@ExcelNovice Just put it in words. 1) X = name, match Sheet1 = name. 2) Or X....? 3) OR X... AND Sheet1....?

[ExcelNovice]

Hi ! Sorry I got confused over S1. Let me explain this in a less pro way in my question above.

[ejbytes]

@ExcelNovice Just describe it Page X, S. We know that S2 is the new sheet. X is the List. Sheet1 is the reference. What on X matching what on S? Just describe in words. Your If statement tells the story that is working according to the If statement. But if you are not even asking the right logical question in your if statement, than that's a problem.

[ejbytes]

@ExcelNovice It's easier to ask the question. Are you saying this? If the name on the List (XSheet), matches a name on the Data Sheet (Sheet1 where you could easily rename this tab Data) and it's "Active" then copy it. Name match and Active? That would make sense. BUT! But, if that's true, Kris isn't on The X List. You have to explain that.

[ejbytes]

@ExcelNovice I changed the solution again, all new code. Copy all and paste it in.