G
GRIFFO
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.
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.