Is there a formula for this?

[Renee]

I have an Excel spreadsheet with 2 columns. The first
column has a Box number (example: 01245). The second
column has the file numbers that are in that particular
box (example: 821-873). So, in other words, box #01245
contains files 821, 822, 823, 824, etc. all the way to
873.

Obviously it's difficult to find a particular file number
if we don't know what file number range it's in. Rather
than list each file number separately (Example: first
column I would list the box number and then in the 2nd
column list the first file #, then I would go down to the
next row and list the box # again with the next file #
and so forth) in order to search for a specific file #.

Is there a formula of some kind whereby I can continue to
include the file # ranges (example: 821-873) but be able
to do a search to find one specific file number
(example: I want to find file # 846)? Is this possible
and if so, how do I do it?

Thanks for any help anyone can give me!

 

[Frank Kabel]

Hi
see your post in Excel.misc
P.S.: please don't multipost

 

[Chip Pearson]

Renee,

Here is some code to find the box number based on the file
number.

Dim Arr As Variant
Dim Hi As Integer
Dim Lo As Integer
Dim FileToFind As Integer
Dim Rng As Range
Dim BoxNum As Variant

FileToFind = 1 '<<<< CHANGE
For Each Rng In Range("B1:B100") '<<<<< CHANGE RANGE
Arr = Split(Replace(Rng.Text, " ", ""), "-")
Lo = Arr(LBound(Arr))
Hi = Arr(UBound(Arr))
If Lo <= FileToFind And FileToFind <= Hi Then
BoxNum = Rng(1, 0)
Exit For
End If
Next Rng
MsgBox BoxNum


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Cindy]

This is probably not the most graceful way to handle this
but how about using two columns to show your file
numbers. Then in the column next to it use this formula:
=IF(AND($D$1>=B3,$D$1<=C3),"*","") Using D1 as an input
area for what file you are looking for. Copy this
formula down for as many rows as you have and then use a
simple filter to find all the *. If you have accidently
used a number more than once it will show you more than
one otherwise it will just show the box number and file
numbers you are looking for.


19
Box # From To Find
1 1 5
2 6 9
3 10 12
4 13 15
5 16 20 *
6 19 23 *