Gaps In Bin Locations

[Tiziano]

I have this long list of bin locations and want to find out where the gaps
are (i.e. bin locations that are not on the list).

Our six-character bin location codes are made up as follows:
* Sector (ex.: A), range is A-H
* Sub-sector (ex.: 1), range is 1-3
* A dash "-"
* Column Number (ex.: 01), range is 01-25
* Row Code (ex.: A), range is A-I

So, a complete bin location code would look like this: A1-01A, or C3-12E.

I want to check for gaps based on the very last character of the bin
location code, i.e. the Row Code, within the above-mentioned parameters.

For example, if the list had B1-01C and B1-01E but not B1-01D, then I would
need to know that there is no "B1-01D". I need to be able to sort the
results, so a complete list of six-character bin location codes missing is
probably the best thing.

I don't know if what I am asking is possible... Thanks for your
suggestions.

 

[Herbert Seidenberg]

Assuming your data looks like binA,
create binC with the formula
=LEFT(binA,5)

binA binC
D3-05G D3-05
F3-11C F3-11
A3-07F A3-07
B1-18B B1-18
B1-18A B1-18
G1-05C G1-05
A3-07B A3-07
H2-14I H2-14
C2-25H C2-25
C1-17F C1-17
A3-07E A3-07
A3-01G A3-01
A3-07D A3-07
B1-18E B1-18
F3-11F F3-11
D3-05H D3-05
E2-01F E2-01
C3-12I C3-12
H2-18B H2-18
A3-07C A3-07
F3-11E F3-11
E2-01D E2-01
C1-17C C1-17
B1-18C B1-18
D3-05A D3-05
A3-01A A3-01
C3-12E C3-12

Create binB from binC using Advanced Filter, Unique Records.
Create a column header (A to I) and name it coln.
Fill the array with this formula:
=IF(COUNTIF(binA,binB&coln)=0,binB&coln,"")
These are the gaps.

binB A B C D E F G H I coln
D3-05 D3-05B D3-05C D3-05D D3-05E D3-05F D3-05I
F3-11 F3-11A F3-11B F3-11D F3-11G F3-11H F3-11I
A3-07 A3-07A A3-07G A3-07H A3-07I
B1-18 B1-18D B1-18F B1-18G B1-18H B1-18I
G1-05 G1-05A G1-05B G1-05D G1-05E G1-05F G1-05G G1-05H G1-05I
H2-14 H2-14A H2-14B H2-14C H2-14D H2-14E H2-14F H2-14G H2-14H
C2-25 C2-25A C2-25B C2-25C C2-25D C2-25E C2-25F C2-25G C2-25I
C1-17 C1-17A C1-17B C1-17D C1-17E C1-17G C1-17H C1-17I
A3-01 A3-01B A3-01C A3-01D A3-01E A3-01F A3-01H A3-01I
E2-01 E2-01A E2-01B E2-01C E2-01E E2-01G E2-01H E2-01I
C3-12 C3-12A C3-12B C3-12C C3-12D C3-12F C3-12G C3-12H
H2-18 H2-18A H2-18C H2-18D H2-18E H2-18F H2-18G H2-18H H2-18I

To sort gaps, convert array to column.
Search this site for how-to.

 

[Tiziano]

Works great, Herbert!
If the number of bin locations in column "binA" changes every time that I
import fresh data into the spreadsheet, how do I make the range names "binA"
and "binB" dynamic so that formula
=IF(COUNTIF(binA,binB&coln)=0,binB&coln,"") works every time?
Thanks.

 

[Herbert Seidenberg]

Making the ranges dynamic will not buy you much,
as long as there is the intervening step of Advanced Filter.
A VBA program is probably a better solution. Anyone?

Dynamic Ranges explained:
http://www.contextures.com
Here is my method of making binA dynamic:
Select a range of 5400 cells (8*3*25*9) that overlaps binA
and name it binAT.
Insert > Name > Define > Names > binA
Refers To: =INDEX(binAT,1):INDEX(binAT,COUNTA(binAT))
Redefine binB and binC in a similar way.

 

[Tiziano]

Thanks, Herbert.