Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check number and return code value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check number and return code value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if Numer is less or great then a fixed number and return a v | Excel Worksheet Functions | |||
VB code to check number in Rectangle box | Excel Programming | |||
Excel check one column for highlighting and return number | Excel Discussion (Misc queries) | |||
How to return the Number of shares for a given stock code for a given portfolio | Excel Discussion (Misc queries) | |||
Excel 2002 VBA code to return a page number within a worksheet | Excel Programming |