Hi,

I have a bit of a complex one that I assume would require VBA.
The scenario involves number analysis and returns one or many codes.

Here is the data I have:
Three columns, the first column has a number, the second column has another
number and the third column has a code.

One data set (Sheet 1) that has:
Column A | Column B | Column C
Row 1 Number Range Start | Number Range End | Code
Row 2 240140000 240148999 MTHN
Row 3 240149000 240149999 CRDF
Row 4 240150000 242199999 MAIT
Row 5 242200000 242209999 WLGG
Row 6 242210000 242210999 WLGG
Row 7 242211600 242211699 WLGG
Row 8 242211700 242211799 UNAN
Row 8 242216000 242216099 WRLA

I then have a second list of data (Sheet 2) that has two number ranges, for
example:

Column A | Column B | Column C
Row Number | Number Range Start | Number Range End
Row 1 240160000 242199999
Row 2 242205555 242209999
Row 3 242216010 242216099

and this is the query/test I want to apply:

For each number between Sheet 2, row 2, column B and column C
(that is 240160000 - 242199999 inclusive)
do a test to see what range it falls between in Sheet 1
and the return the Code.

I would then need it to move to row 3 and do the same, for every row that
has numbers. (There may be many). I would rather an automatic process than
to have to do one row at a time.

I note that there will be many many codes returned, and I would then filter
those codes for unique entries only.

It sound like it may be an Access thing rather than a Excel thing, but I am
hoping you smart people may be able to use some For Next loops or something
to assist.

--
Any assistance is appreciated.
Griffo

You can use worksheet function to get this

Assuming you have the number range start in ColB of Sheet2; try the below
formula in Sheet2 D1

=VLOOKUP(B2,Sheet1!$A$2:$C$10,3,TRUE)

B2 refers to Sheet2 B2 or the number range start
Sheet1!$A$2:$C$10 refers to your data in Sheet1 A:C

If this post helps click Yes
---------------
Jacob Skaria


"GRIFFO" wrote:

> Hi,
>
> I have a bit of a complex one that I assume would require VBA.
> The scenario involves number analysis and returns one or many codes.
>
> Here is the data I have:
> Three columns, the first column has a number, the second column has another
> number and the third column has a code.
>
> One data set (Sheet 1) that has:
> Column A | Column B | Column C
> Row 1 Number Range Start | Number Range End | Code
> Row 2 240140000 240148999 MTHN
> Row 3 240149000 240149999 CRDF
> Row 4 240150000 242199999 MAIT
> Row 5 242200000 242209999 WLGG
> Row 6 242210000 242210999 WLGG
> Row 7 242211600 242211699 WLGG
> Row 8 242211700 242211799 UNAN
> Row 8 242216000 242216099 WRLA
>
> I then have a second list of data (Sheet 2) that has two number ranges, for
> example:
>
> Column A | Column B | Column C
> Row Number | Number Range Start | Number Range End
> Row 1 240160000 242199999
> Row 2 242205555 242209999
> Row 3 242216010 242216099
>
> and this is the query/test I want to apply:
>
> For each number between Sheet 2, row 2, column B and column C
> (that is 240160000 - 242199999 inclusive)
> do a test to see what range it falls between in Sheet 1
> and the return the Code.
>
> I would then need it to move to row 3 and do the same, for every row that
> has numbers. (There may be many). I would rather an automatic process than
> to have to do one row at a time.
>
> I note that there will be many many codes returned, and I would then filter
> those codes for unique entries only.
>
> It sound like it may be an Access thing rather than a Excel thing, but I am
> hoping you smart people may be able to use some For Next loops or something
> to assist.
>
> --
> Any assistance is appreciated.
> Griffo