Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cell in one column to value in another
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cell in one column to value in another
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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cell in one column to value in another
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)) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCHING A VALUE IN A CELL TO A COLUMN OF VALUES IN ANOTHER SHEET | Excel Discussion (Misc queries) | |||
Is there a function to get column # of cell with matching value | Excel Worksheet Functions | |||
matching from one column to another | Excel Discussion (Misc queries) | |||
find a cell matching separate column and row values | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |