Matching, identifying, and updating worksheets

[artemis_f0wl]

Hello and thanks for your help!

I am quite new to excel and have been given the task of creating a file which contain the following:

A designated master list which holds a predefined list of "approved" items.
A worksheet which contains a list of incoming items for a specific batch.

The idea is that incoming items listed can be cross referenced with the master list. If the item is on the list, it is given a "green light" or "ok flag". If the item is not on the list, it is designated for manual review. Once reviewed, the item should be given a "pass flag" or a "fail flag". If the item passes, it should then be added to the master list.

An idea that would make things even more ideal would be to create a master black list as well. When an item is given the "fail flag", it should then be added to the master black list.

Over time, a new list of items should come in and automatically be identified (in majority at least) as "good" or "bad".

Please let me know if this is possible. I'm not necessarily asking for the solution (though it would be appreciated), but would love to get some sources to find the correct tools to accomplish this goal.

Thanks again!

[JBeaucaire]

I'll let the programming gurus respond about VBA answers, but everything you've described is manageable with a COUNTIF formula on your incoming sheet. To check an item in A2:

=IF(COUNTIF('MasterList'!A:A,A2)>0,"good","review")

Copy that formula down the whole data set you're checking. Sort by the added column and all the "good" are together and can be deleted.

Run the remaining items through your "test" and make your changes to name it "pass" or "fail". Then sort again by this column. Copy all the "pass" items (now grouped together) over onto the MasterList, and use the remaining "fail" items to start a new "FailList" sheet.

Once the FailList is started, the formula above to test new items changes to:

=IF(COUNTIF('MasterList'!A:A,A2)>0,"good",IF(COUNTIF('FailList'!A:A,A2)>0,"bad","review"))

[shg]

Artemis, please read the forum rules about thread titles before posting again.

[artemis_f0wl]

Thanks for the reply. The solution is still escaping me.

I won't post here again- I prefer communities where the mods are interested in helping over nit-picking for nothing.