Matching cell in one column to value in another

[Bruce]

We have started using a variant of a bar code reader to
read the serial numbers on parts that we process. The way
it works is that the parts arrive in boxes of 10 in some
particular order. As they are unpacked they are scanned
one by one. The scanning program inserts the serial
number into an Excel spreadsheet, starting at a selected
cell (say A2), then advances to A3. The next part is
scanned, its serial number is inserted into A3, A4 is
selected, another part is scanned, and so forth. There
could be up to 500 parts. Each group of 10 cells would
have a border around it, labeled Box 1, Box 2, etc. Cells
A2 - A11 would be labeled Box 1, A12 - A21 would be Box 2,
and so forth.
As the parts are processed they do not stay in any
particular order. When processing is completed the parts
are again scanned, starting at a selected cell such as
B2. The idea is that as each part is scanned, the
corresponding serial number from column A is highlighted
in some way. If the first serial number scanned at the
end of processing is 123456, and 123456 is in A15 from the
original scan, Excel will either highlight A15 or will
indicate in B2 that its match is in A15 (or maybe even
show something in B2 such as "Box 2, Compartment 4").
One final point is that if highlighting is used, it would
have to be canceled as each new part is scanned, or it
could become difficult to see which new cell was
highlighted. In other words, only one highlighted cell at
a time.
I am open to suggestion. I don't even know if VBA is the
best approach. I do know that this needs to happen in
Excel.

 

[Guest]

Put this formula in column C to get Box and Compartment number. This was tested on 8 boxes of 2 compartments each, but should work with more boxes of ten compartments. Just replace each '2' with a '10'

="Box "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,MATCH(B1,A$1:A$8)/2,FLOOR(MATCH(B1,A$1:A$8)/2,1)+1)&"; Compartment "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,2,MOD(MATCH(B1,A$1:A$8),2))

 

[Bruce]

Thanks. That solved the problem perfectly. I tested with
three boxes of ten compartments each, using a sequence of
numbers 123, 124, etc. sorted in random order for column
A, and the same sequence sorted in another order for
column B. This was to simulate the random order of the
parts at each end of the process. I found that the part
of the formula MATCH(B1,A$1:A$30) needed to be MATCH
(B1,A$1:A$30,0) since the lookup array could be in any
order. At least, that's why I think it worked based on my
study of the MATCH worksheet function.
I was able to fairly easily comprehend the MOD, MATCH, and
FLOOR worksheet functions individually, but it took me
quite a while to sort out how they work together to
produce the desired result. I really appreciate the time
you took to develop and test the formula. I did not know
that Excel even had those capabilities, so I doubt I would
have been able to devise such an effective solution on my
own. Thanks again.

-----Original Message-----
Put this formula in column C to get Box and Compartment

number. This was tested on 8 boxes of 2 compartments
each, but should work with more boxes of ten
compartments. Just replace each '2' with a '10'.

="Box "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,MATCH

(B1,A$1:A$8)/2,FLOOR(MATCH(B1,A$1:A$8)/2,1)+1)&";
Compartment "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,2,MOD(MATCH
(B1,A$1:A$8),2))