Gaps In Bin Locations
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.
|