Gaps In Bin Locations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tiziano - 20 Aug 2006 16:59 GMT

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.

Signature

Tiziano

Reply to this Message

Herbert Seidenberg - 20 Aug 2006 21:05 GMT

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.

Reply to this Message

Tiziano - 21 Aug 2006 04:48 GMT

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.

Signature

Tiziano

> Assuming your data looks like binA,
> create binC with the formula
[quoted text clipped - 51 lines]
> To sort gaps, convert array to column.
> Search this site for how-to.

Reply to this Message

Herbert Seidenberg - 21 Aug 2006 18:42 GMT

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.

Reply to this Message

Tiziano - 22 Aug 2006 02:48 GMT

Thanks, Herbert.

Signature

Tiziano

> Making the ranges dynamic will not buy you much,
> as long as there is the intervening step of Advanced Filter.
[quoted text clipped - 8 lines]
> Refers To: =INDEX(binAT,1):INDEX(binAT,COUNTA(binAT))
> Redefine binB and binC in a similar way.

Reply to this Message