vLOOKUP help

[jwdemo]

I am wanting to compare 2 sheets and return only the lines that contain matching UPC values to sheet 3.

Sheet 1 is a long list of products, most of which we do not carry in our store. The sheet contains corrections to some products that have recently been made. These corrections are marked in column A with the reason and are highlighted in green. All items that did NOT change have no data in column A.
Sheet 2 is a list of only products we carry.
Sheet 3 I would like to be a list of items that show up on both sheets and that had a change associated with them.

I would like to take the first UPC in Sheet 2 column C (C2) and look in the entire Sheet 1 column H for a match. If there is a match with an item that contains a change, I'd like the entire line copied into sheet 3.

I can use the excel filter tool to show only those with changes but they don't always start on line 1. Would the vLOOKUP be able to only use the visible items on sheet 1?

 

[Peter_SSs]

Since I'm not sure how to post a screenshot ..

My signature block below has several suggestions for posting small screen shots and where to test them.

This would most easily be achieved by a macro, or manual steps that I could outline if needed.

This macro assumes that Sheet3 already exists but anything on it can be cleared.
Test in a copy of your workbook.
Post back with more details if it does not do what you want.

Sub Extract_Changes()
  Dim lc As Long
  Dim rCrit As Range
  
  Sheets("Sheet3").UsedRange.ClearContents
  With Sheets("Sheet1")
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set rCrit = .Cells(1, lc + 1).Resize(2)
    rCrit.Cells(2).Formula = "=AND(A2<>"""",ISNUMBER(MATCH(H2,Sheet2!C:C,0)))"
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, lc).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, _
      CopyToRange:=Sheets("Sheet3").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With
End Sub

 

[jwdemo]

Is this comparing column D in Sheet1 and in column H on Sheet2?

 

[Peter_SSs]

Is this comparing column D in Sheet1 and in column H on Sheet2?

No, sorry, I missed that change and was using the columns as described in post #1

I would like to take the first UPC in Sheet 2 column C (C2) and look in the entire Sheet 1 column H for a match.

Try just making these changes shown in red.

rCrit.Cells(2).Formula = "=AND(A2<>"""",ISNUMBER(MATCH(D2,Sheet2!H:H,0)))"

 

[jwdemo]

That did it...thanks! Would it be possible to get the macro to paste the line from Sheet 2 instead of the one from Sheet 1? It contains a slightly different bit of information.

 

[Peter_SSs]

That did it...thanks! Would it be possible to get the macro to paste the line from Sheet 2 instead of the one from Sheet 1? It contains a slightly different bit of information.

Try this version.

Sub Extract_Changes_v2()
  Dim lc As Long
  Dim rCrit As Range
  
  Sheets("Sheet3").UsedRange.ClearContents
  With Sheets("Sheet2")
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set rCrit = .Cells(1, lc + 1).Resize(2)
    rCrit.Cells(2).Formula = "=COUNTIFS(Sheet1!D:D,H2,Sheet1!A:A,""<>"")"
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, lc).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, _
      CopyToRange:=Sheets("Sheet3").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With
End Sub

 

[jwdemo]

It doesn't seem like it is copying everything over that is on Sheet2. For instance, some of the text in Column N is missing and some of the formatting is different (highlighted cells, etc.) throughout Sheet 3.

 

[Peter_SSs]

For instance, some of the text in Column N is missing

I don't understand what you mean by that.
- Cells have nothing in them that should have text?
- Cell have some text but not all the text that was in Sheet2?
- Something else?

...and some of the formatting is different (highlighted cells, etc.) throughout Sheet 3.

Can you clarify this too please?


Any chance of some dummy sample data that demonstrates the above problems? My signature block below has suggestions for posting small screen shots.

 

[jwdemo]

Cells have nothing in them that should have text?

Yes.

Some of the cells have text in them but after they are copied to the other sheet they seem to lose the text. Also, some cells in the sheet are highlighted green but when copied over they don't have the highlighting.

 

[Peter_SSs]

Some of the cells have text in them but after they are copied to the other sheet they seem to lose the text. Also, some cells in the sheet are highlighted green but when copied over they don't have the highlighting.

My sample data is not doing that, so ..

Any chance of some dummy sample data that demonstrates the above problems? My signature block below has suggestions for posting small screen shots.

 

[jwdemo]

Peter,

I can't seem to get the Excel Jeanie towork on my Excel 2013. Would it be possible for me to email the file to you to look at?