How to compare to a list and return missing list items in summary. (if/and)

Hello all,

I currently receive a data dump from a client which is a list of samples, with a corresponding test that needs to be completed on it. Each sample has anywhere between 1 and 10 test that might be assigned to it. The sample number is repeated in the data dump, for each unique test that is requested. I have attached a mock up file which shows what I am trying to accomplish.

Worksheet 1 is where I will paste the raw data provided. Worksheet 2 is a list of 10 possible tests that could be assigned to this sample. Worksheet 3 is what I want my result to be. Columns B-K are not required to show this way in my result, I just wanted to show a possible intermediate result if it is easier to reach my final result in this way.

Ultimately what I would like my final result to show is column A and column L (hide B-J to see what I mean)

I have played with if/and but I am unable to get any results and I am not confident that I am going about it the correct way. Other than my data dump worksheet, I am very open to rearranging the way it works or looks as long as the final result is a summary of which tests need to be removed from each sample.

Thoughts? Suggestions? Mock-ups? Any help you could provide would be great.

Les

Sorry for the confusion. The list sheet is just a reference off all test names for a formula to compare against. This would allow me to later add another test to "look for" should it be needed.

As far as a result, what I am looking for is just a summary of tests for each sample that are not in the data dump. Just for context, once i receive the data dump from my client, I would like to be able to quickly see what tests are NOT NEEDED for each sample out of the 10 possible.

I hope this helps. Please let me know if you have other questions.

Les

You are using jargon familiar to you to describe what you want, but I know nothing about your jargon. For the example file you posted, what exact list do you want...

Red Test, Grey Test, Brown Test

or...

Blue Test, Green Test, Yellow Test, Pink Test, Orange Test, Gold Test, Purple Test

Sorry, I'm not a power user so I may not have all the correct lingo, I know no other way to describe it. I'm not sure I understand your question.

I have uploaded a new copy of the SS hoping it will be clearer.

You rock! That's awesome. It worked on the mock up and worked with my actual small batch of data on first test. The only issue I had is that when I went full batch of raw data there was more than 500 rows and it seems to stop working after 500 rows. If this is a limitation, I can make it work by doing it in batches, but the obvious question is, is it a limitation or just a simple tweak of the code?

Thanks so much Mumps1.

Originally Posted by bigtunelover

The only issue I had is that when I went full batch of raw data there was more than 500 rows and it seems to stop working after 500 rows.

This should work.

Please Login or Register  to view this content.

Originally Posted by sintek

My take on this one...

Please Login or Register  to view this content.

This is also working great! So as to not mess with your beautiful code, is there an easy tweak I can pop in that reports "none" instead of a blank cell if there are no tests on the result WS?

Thanks a bunch!