Need to do a data check - not sure the best way.

[cmjat]

Okay, I'm working with a team that does QC checks. One person does the work,
another QC's the work and a third QC's the QC. To further complicate matters
some clients go through a first pass QC, some also go through a second pass
QC and a select few go through a third pass QC. If I knew the client name I
could determine which checks the jobs needed to go through but that's not
easy information to get since the job names are pretty cryptic. However, all
of these are done in separate spreadsheets and I know how to generate a list
of the spreadsheets. Let me provide some specifics so you get a better idea
what I have and what I need to do.

Each QC is in a separate spreadsheet so for a given job name I might have a
list that looks like:

OSH-100327P QC1
OSH-100327P QC1checked
OSH-100327P QC2
OSH-100327P QC2checked
OSH-100327P QC3
OSH-100327P QC3checked

In this case I know that all the files exist and are ready for me to collect
the data from within the spreadsheets. I need a formula to tell me if all the
files exist and I'm not sure what the best way is to do that.

To deal with the more complex scenarios let's say OSH-100327P really only
gets 2 QCs, I need the formula to check to see if QC1, QC2 and QC3 exist. If
only QC1 and QC2 exist then it only needs to see if QC1checked and QC2checked
exist. If they do, it should return READY (or some equivalent). If not, it
should return NOT READY (or some equivalent). The below example should return
NOT READY.

OSH-100327P QC1
OSH-100327P QC1checked
OSH-100327P QC2

Here are all 6 possible variations where the job name will change but I will
always know what the job name is:

Variation 1 - NOT READY:
HD-100321-ST032110 QC1

Variation 2 - READY:
TSA-100328 QC1
TSA-100328 QC1checked

Variation 3 - NOT READY:
P-100325sab QC1
P-100325sab QC1checked
P-100325sab QC2

Variation 4 - READY:
BT-100326 QC1
BT-100326 QC1checked
BT-100326 QC2
BT-100326 QC2checked

Variation 5 - NOT READY:
M-100328GA QC1
M-100328GA QC1checked
M-100328GA QC2
M-100328GA QC2checked
M-100328GA QC3

Variation 6 - READY:
CT-100326FRN QC1
CT-100326FRN QC1checked
CT-100326FRN QC2
CT-100326FRN QC2checked
CT-100326FRN QC3
CT-100326FRN QC3checked

In an effort to be clearer, I have one spreadsheet for each QC and each QC
check for every single job - so hundreds of spreadsheets. I can generate a
list of the jobs that are outstanding (in process and ready for me to record
as being done.) I can generate a list of all the spreadsheet names which I've
been breaking into two components: jobname and QC/status as illustrated
above.

Now I just need a formula to tell me which ones are ready so I don't have to
do it visually/manually.

Thanks in advance for any help.

 

[Dave Peterson]

So each job has at least the QC# entry, right.

And the data is nicely sorted -- QC# above the QC#Checked, right?

I think that this would work.

With the data starting in Row 2 (headers in Row 1), put this in C2:
=IF(LEN(B2)>9,"",IF(AND(LEFT(B2,2)="qc",B3=B2&"checked"),"Ok","Not Checked"))

And drag the formula down the range as far as you need.

Then apply data|filter|autofilter to show the ok or not checked rows.