Spreadsheet: 126497d1319723101-excel-2007-vba-help-needed-to-create-a-macro-to-create-a-unique-number-sample_archive_index
Sheet: Counting Pivot Table

EXAMPLE:
Location | Shelf/Box# | Archive Index # | Study # | GLP | Terminated | Year Completed | Title | Content Description | [NAME] | Date Placed Inactive Storage |  | Date Placed in Active Archive 2008 | Archived by (SAW or DJC)
A | 7 | 2007-090-002 | PHI-2007-090 | Y |  |  | A 28-Day Toxicity Study of GM-HRA Protein Administered by Diet to CD-1 Mice | PI Report: Diet Characterization | PI Report Title: Characterization of the Homogeneity and Stability of GM-HRA Protein in Rodent Diets using Quantitative ELISA [PI: [NAME], signed 12/14/2007] |  |  | 2008-01-04 00:00:00 | saw
A | 7 | 2007-090-003 | PHI-2007-090 | Y |  |  | A 28-Day Toxicity Study of GM-HRA Protein Administered by Diet to CD-1 Mice | Sample Processing Notebook | Sample Processing Data |  |  | 2008-01-04 00:00:00 | djc
A | 7 | 2007-090-004 | PHI-2007-090 | Y |  |  | A 28-Day Toxicity Study of GM-HRA Protein Administered by Diet to CD-1 Mice | Protein Laboratory Data Notebook, ELISA Data Volumes 1 and  2 | ELISA Data Volume 1 [Study Info; GM-HRA ELISA Method; [NAME]; GM-HRA Results]; ELISA Data Volume 2 [Homogeneity 6-28-07; Sample Data 6-28-07; GM-HRA 6-28-07; Stability Day 0 7-9-07; Sample Data 7-9-07; GM-HRA 7-9-07; Stability Day 28; Sample Data 8-3-07; [NAME]; 8-9-07 Sample Data; 8-9-07 Report Day 0 & 28] |  |  | 2008-01-04 00:00:00 | djc
I | 172371672 | 2007-130-001 | PHI-2007-130 | N | N | 2007 | Characterization of the Effect of Heat Treatment on the Immunoreactivity of the ZM-HRA protein using Western Blot Analysis | Protocol, Final Report, Study Records | Study Director: [NAME] [signed 10/15/2007] | 2007-11-13 00:00:00 |  | 2008-01-05 00:00:00 | djc
I | 172371672 | 2007-131-001 | PHI-2007-131 | N | N | 2007 | Characterization of the Effect of Heat Treatment on the Immunoreactivity of the Glyphosate N-acetyltransferase 4621 ([NAME]) Protein using Western Blot Analysis | Protocol, Final Report, Study Records | Study Director: [NAME] [signed 10/15/2007] | 2007-11-13 00:00:00 |  | 2008-01-06 00:00:00 | djc
I | 172371672 | 2007-133-001 | PHI-2007-133 | N | Y | 2007 | Field Production of Maize Grain Containing the Combined Trait Product DAS-Ø15Ø7-1xDAS-59122-7xSYN-BTØ11-1xDP-Ø9814Ø-6: South America Location | Study Protocol, Termination Amendment | Study Plan Coordinator: [NAME] [signed 09/24/2007] | 2007-11-13 00:00:00 |  | 2008-01-07 00:00:00 | saw
 |  |  |  |  |  |  |  |  |  |  | Total # [NAME] Processed
Study # | Archived by (SAW or DJC) |  | Count of Study # | Archived by (SAW or DJC)
PHI-2007-090 | saw |  | Study # | djc | saw | Grand Total of Packages Processed
PHI-2007-090 | djc |  | PHI-2007-090 | 2 | 1 | 3
PHI-2007-090 | djc |  | PHI-2007-130 | 1 |  | 1
PHI-2007-130 | djc |  | PHI-2007-131 | 1 |  | 1
PHI-2007-131 | djc |  | PHI-2007-133 |  | 1 | 1
PHI-2007-133 | saw |  | Grand Total of Packages Processed | 4 | 2 | 6
 |  |  | # different studies (djc)
 |  |  | # different studies (saw)
DIRECTIONS:
1.  From main Archive Index worksheet filter the column "Date Placed in Active Archive" (column M) on NonBlanks
2.  [NAME] the column "Study #" (column D) (pink above) into column A as above (matching pink areas) {We're trying to get only the entries visible on the worksheet and avoid the hidden rows created by using the filter.}
3.  [NAME] the column "Archived by" (column N) (purple above) into column B as above (matching purple areas) {We're trying to get only the entries visible (that correspond with the study numbers)  on the worksheet and avoid the hidden rows created by using the filter.}
4.  Run the pivot table wizard.  Click anwhere on the pivot table and right click to get "Pivot table Wizard" as an option, then click back in order to be able to adjust the data area to include all of the pink and purple cells in columns A and [NAME] the pivot table in "Exisiting Worksheet" and hit Finish.
5.  The pivot table above should be updated with the new information.
6.  Then update the counts being run in cells [NAME] and D20, by adjust the area in the pivot table that they are counting entries in.  See the formula in the cell.