Looking for matches/non-matches

[duke6513]

Hi all..... While I do know a little about Excel, I am hoping the Guru's here can help this comparable novice. This is what I am trying to do and am hoping someone here can help me. Each month I get an inventory report (excel file - lets call it "spareinv") listing the asset # & serial number of the equipment I "should have". Let's say the asset number is in "B" column and serial number is in "C" column. I have come up with a way to do a "physical" inventory each month with a bar code scanner in which I scan the asset tag and serial number which is on the equipment in bar code format. Currently I scan these to another excel spreadsheet (let's call this one "Physical"). I copy and paste the info from "physical" sheet into the bottom of the "spareinv" sheet using the same columns. Then I run a conditional format ("countif" formula) on column "B" (asset#) to look for duplicate assets (formatting duplicates yellow) and another conditional format, also "countif" on the "C" (serial#) formatting duplicates green. Obviously, the ones that match (colored) have been verified physically, the unmatched ones have not been physically verified. Doing both columns allows me to check for database input errors. In a nutshell, I know there is an easier way to do this, I just can't figure it out. For instance I know I don't need to do the cut and paste between the two worksheets. What I would like is a formula or template to look at both sheets, take the matches and paste them in another spreadsheet ("verified"), and paste the unmatched (unverified") in yet another spreadsheet. Anyone have any thoughts? Any help you can give would be VERY appreciated!! THANK YOU! Sorry this is so long!

 

[acw]

Hi

How about

Sub aaa()
 For Each ce In Sheets("Physical").Range("b2:b" & Sheets("Physical").Cells(Rows.Count, "B").End(xlUp).Row)
  If WorksheetFunction.CountIf(Sheets("spareinv").Range("b:b"), ce) > 0 Or WorksheetFunction.CountIf(Sheets("spareinv").Range("c:c"), ce.Offset(0, 1)) > 0 Then
   Range(ce, ce.Offset(0, 1)).Copy Destination:=Sheets("verified").Range("b" & Sheets("verified").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row)
  Else
   Range(ce, ce.Offset(0, 1)).Copy Destination:=Sheets("nonverified").Range("b" & Sheets("nonverified").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row)
  End If

 Next ce

 For Each ce In Sheets("SpareInv").Range("b2:b" & Sheets("SpareInv").Cells(Rows.Count, "B").End(xlUp).Row)
  If WorksheetFunction.CountIf(Sheets("physical").Range("b:b"), ce) = 0 And WorksheetFunction.CountIf(Sheets("physical").Range("c:c"), ce.Offset(0, 1)) = 0 Then
   Range(ce, ce.Offset(0, 1)).Copy Destination:=Sheets("nonverified").Range("b" & Sheets("nonverified").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row)
  End If
 Next ce
End Sub

Your spareinv sheet has data in columns B and C. Your Physicals sheet has data in columns B and C (do not paste it onto spareinv). You also have 2 output sheets Verified and NonVerified.

It tests all the data in Physical against spareinv. Matches go to Verified and non matches to NonVerified. It then tests spareinv and any non matches go to NonVerified (as matched will already have been covered).


Tony