A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 08:48 PM posted to microsoft.public.excel.worksheet.functions
cmjat
external usenet poster
 
Posts: 1
Default Need to do a data check - not sure the best way.

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.
--
Jen
  #2  
Old May 29th, 2010, 12:02 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Need to do a data check - not sure the best way.

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&"che cked"),"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.



cmjat wrote:

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.
--
Jen


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.