This one has got me stumped and will take some explaining... Here’s it is.
Our Document Control team has been using a spreadsheet to generate document numbers for two years
. These document numbers are comprised of three parts (site, type and number). The number part is unique to the site and number. Here's an example of how it essentially looks.
Site Type No Full number
SiteA PDF 1 SiteA-PDF-1
SiteA PDF 2 SiteA-PDF-2
SiteA PDF 3 SiteA-PDF-3
SiteA DOC 1 SiteA-DOC-1
SiteA DOC 2 SiteA-DOC-2
SiteA DOC 3 SiteA-DOC-3
SiteA DOC 4 SiteA-DOC-4
SiteA DOC 5 SiteA-DOC-5
SiteB PDF 1 SiteB-PDF-1
SiteB PDF 2 SiteB-PDF-2
SiteB PDF 3 SiteB-PDF-3
SiteB DOC 1 SiteB-DOC-1
SiteB DOC 2 SiteB-DOC-2
SiteB DOC 3 SiteB-DOC-3
SiteB DOC 4 SiteB-DOC-4
SiteB DOC 5 SiteB-DOC-5
SiteB DOC 6 SiteB-DOC-6
Now, because they have made a pigs ear of it, they've ended up with a situation in which some numbers have been missed and I need to fix it. Here's what I'm doing.
Site Type No Site-Type Full number Count of Site-Type Max of Site-Type
SiteA PDF 1 SiteA-PDF SiteA-PDF-1 1 3
SiteA PDF 2 SiteA-PDF SiteA-PDF-2 2 3
SiteA PDF 3 SiteA-PDF SiteA-PDF-3 3 3
SiteA DOC 1 SiteA-DOC SiteA-DOC-1 1 5
SiteA DOC 3 SiteA-DOC SiteA-DOC-3 2 5
SiteA DOC 4 SiteA-DOC SiteA-DOC-4 3 5
SiteA DOC 5 SiteA-DOC SiteA-DOC-5 4 5
SiteB PDF 1 SiteB-PDF SiteB-PDF-1 1 3
SiteB PDF 2 SiteB-PDF SiteB-PDF-2 2 3
SiteB PDF 3 SiteB-PDF SiteB-PDF-3 3 3
SiteB DOC 2 SiteB-DOC SiteB-DOC-2 1 6
SiteB DOC 3 SiteB-DOC SiteB-DOC-3 2 6
SiteB DOC 5 SiteB-DOC SiteB-DOC-5 3 6
SiteB DOC 6 SiteB-DOC SiteB-DOC-6 4 6
SiteB DOC SiteB-DOC
I've developed a formula that counts the number of instances of a particular Site-Type combination so I can simply add one to it to get the next number. Unfortunately, because of the numbers they've missed off, it won't work. The 'Count of Site-Type' column shows where the missing numbers cause the issue - they're different to the assigned numbers in some cases. The 'Max of Site-Type' column returns the current highest number that has been assigned to that Site-Type. The two columns show the difference - in some case this is hundreds on the spreadsheet... I call them the 'Document Out of Control' team...
For reference, here are the two formulae..
Count of Site-Type formula (I'd like this to generate the numbers in future when it's been 'fixed')
=COUNTIF(D$2:D2,D2))
Max of site type formula (this
={MAX(IF($D$2:$D$15=D2,$C$2:$C$15))}
I need either:
A formula that will check for missing numbers and add them first before carrying on afterwards
OR
A formula that find the highest existing number for a site and doc type combo and simply add to carry on and forget the missing numbers...
Any ideas?
