Need to do a data check - not sure the best way.
in [Functions]
|
From: cmjat on 28 May 2010 15:48 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
From: Dave Peterson on 28 May 2010 19:02 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. 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
|
Pages: 1 Prev: export data between spreadsheets Next: zero value in cell |