Vlookup help for comparing lists of numbers
March 19, 2009 7:03 AM
I am trying to partially automate a tedious list comparison procedure, I think I should be able to do it through excel without any coding (which I don't know how to do)
I have 2 separate lists of numbers; A & B.
List A is a list of "box tags" that the Inventory system says is physically in a freezer.
List B is a list of "tags" that was processed into boxes that went into the freezer. Each Tag from List B is processed into 2 parts.
So, if everything is correct, then every line # on list B will have 2 corresponding entries on list A.
The possible errors in the system, which have to be reconciled/investigated are:
Error 1:Tag on list B might not have a match on list A
Error 2:Tag on list B might have only one match on list A
Error 3:Tag on list A might not have a match on list B
Errors are introduced into the system because the items move in and out of the freezer and production areas, tracked by an imperfect RFID system.
The highlighted errors are then physically counted/removed from the inventory system.
I had originally set up the two lists in excel, and used a vlookup function to highlight all of the error 3's - but I am stuck at how to create a similar function to identify error 1 & 2's.
Any suggestions about how to set this up would be appreciated. I am trying to stick with excel so that the weekly A & B lists can be copied into the table by an admin position, and the errors for investigation be quickly highlighted.
I have 2 separate lists of numbers; A & B.
List A is a list of "box tags" that the Inventory system says is physically in a freezer.
List B is a list of "tags" that was processed into boxes that went into the freezer. Each Tag from List B is processed into 2 parts.
So, if everything is correct, then every line # on list B will have 2 corresponding entries on list A.
The possible errors in the system, which have to be reconciled/investigated are:
Error 1:Tag on list B might not have a match on list A
Error 2:Tag on list B might have only one match on list A
Error 3:Tag on list A might not have a match on list B
Errors are introduced into the system because the items move in and out of the freezer and production areas, tracked by an imperfect RFID system.
The highlighted errors are then physically counted/removed from the inventory system.
I had originally set up the two lists in excel, and used a vlookup function to highlight all of the error 3's - but I am stuck at how to create a similar function to identify error 1 & 2's.
Any suggestions about how to set this up would be appreciated. I am trying to stick with excel so that the weekly A & B lists can be copied into the table by an admin position, and the errors for investigation be quickly highlighted.
It would require a new column, but this seems like a good use for the COUNTIF function.
in a new column, assuming the data starts in B1 do:
countif(A:A,B1)
Anywhere the result is "0", it's error 1. Anywhere it's "1", it's error 2.
Once you have that, you can either scan down the page to identify by eye, or use the Data->Filter property to look at each error individually (or remove all the good values, and look at all the errors together).
posted by inigo2 at 7:11 AM on March 19, 2009
in a new column, assuming the data starts in B1 do:
countif(A:A,B1)
Anywhere the result is "0", it's error 1. Anywhere it's "1", it's error 2.
Once you have that, you can either scan down the page to identify by eye, or use the Data->Filter property to look at each error individually (or remove all the good values, and look at all the errors together).
posted by inigo2 at 7:11 AM on March 19, 2009
« Older Moving from the UK to US on husband's work visa -... | Need animated visualizations of the Internet and... Newer »
This thread is closed to new comments.
Assuming you have list A in column A from, say, A1 to A100, and list B in column B, you can create column C like this (example for C1):
=countif($A$1:$A$100,B1)
This should return 2. You could use conditional formatting to highlight values other than 2.
posted by Perplexity at 7:10 AM on March 19, 2009