Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
Hi I'm a newbie to writing macro, so could someone please help me with
this. I have 2 sheets in a workbook. Sheet 1 looks like this Column A Column B Column C ABCDE 3000 3099 BDSCXD 4500 4599 UIT 4800 5199 SWEDFE 5300 5399 SmartS 5450 5499 XYZ123 5600 5629 QWER23 5650 5669 Sheet2 looks like this Column A Column B Column C 4523 12345 5621 34543 4545 43534 5326 23453 5629 23432 I need to check the data in Column B (in Sheet 2) above and compare it with Sheet 1, i.e., see if it lies between Column B & Column C (in Sheet 1). For example: "4523" is the first value in Sheet 2, I need to see which row 4523 belongs in Sheet 1, i.e., 4523 lies between 4500 & 4599 (row2 in Sheet1), when it finds the row I need to pick up the name in Column A and put it in Column A of Sheet2. Another example the last value is 5629 and it lies between 5600 & 5629. So basically Column B in Sheet2 is a data that lies between a start and end value (ColumnB & columnC in sheet1). The result will look like this in Sheet2 Column A Column B Column C BDSCXD 4523 12345 XYZ123 5621 34543 BDSCXD 4545 43534 SWEDFE 5326 23453 XYZ123 5629 23432 I don't know if this is of importance, their are more than 15000 rows in Sheet2. Thanks in advance for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
You do not need a macro, a simple formula does it,.
In A1 of Sheet2 =INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,1)) and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hi I'm a newbie to writing macro, so could someone please help me with this. I have 2 sheets in a workbook. Sheet 1 looks like this Column A Column B Column C ABCDE 3000 3099 BDSCXD 4500 4599 UIT 4800 5199 SWEDFE 5300 5399 SmartS 5450 5499 XYZ123 5600 5629 QWER23 5650 5669 Sheet2 looks like this Column A Column B Column C 4523 12345 5621 34543 4545 43534 5326 23453 5629 23432 I need to check the data in Column B (in Sheet 2) above and compare it with Sheet 1, i.e., see if it lies between Column B & Column C (in Sheet 1). For example: "4523" is the first value in Sheet 2, I need to see which row 4523 belongs in Sheet 1, i.e., 4523 lies between 4500 & 4599 (row2 in Sheet1), when it finds the row I need to pick up the name in Column A and put it in Column A of Sheet2. Another example the last value is 5629 and it lies between 5600 & 5629. So basically Column B in Sheet2 is a data that lies between a start and end value (ColumnB & columnC in sheet1). The result will look like this in Sheet2 Column A Column B Column C BDSCXD 4523 12345 XYZ123 5621 34543 BDSCXD 4545 43534 SWEDFE 5326 23453 XYZ123 5629 23432 I don't know if this is of importance, their are more than 15000 rows in Sheet2. Thanks in advance for any help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
Hi Bob,
Question, I'm not sure of this, but the data in Sheet2 is generated by another process (macro), so the number of rows are not known, so this needs to be done automatically, Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
Hi Bob,
The above solution you gave me works only if I have the exact value. This is what I need to do I need to take the value from B1 in Sheet2 and see within what range it lies in Sheet1. The range in Sheet1 is values between ColB & colC. Basically I need to see if B1 in Sheet2 lies between, B1 & C1 in Sheet1, if not see if it lies between B2 & C2 and so forth. So if I look at the first value in Sheet2 it is 4523 (B1). I need to see if 4523 lies between B1 & C1 in Sheet1 (that would be 3000 & 3099), which is not, so I go to the next row in Sheet2 (4500 & 4599) which is true, so I need to get the value from A2 in Sheet1 and put it in A1 in Sheet2 (as we are looking at value in B1) Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
To you above solution, I need something like this
=INDEX(Sheet1!A:A,MATCH(B1,(B1=Sheet1!B:B) & (B1<=Sheet1!C:C), 1))....I know this does not work, but just wanted to give something |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
Well I tested it and I got exactly the results you showed, and it does
exactly what you describe below. So what are you getting? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hi Bob, The above solution you gave me works only if I have the exact value. This is what I need to do I need to take the value from B1 in Sheet2 and see within what range it lies in Sheet1. The range in Sheet1 is values between ColB & colC. Basically I need to see if B1 in Sheet2 lies between, B1 & C1 in Sheet1, if not see if it lies between B2 & C2 and so forth. So if I look at the first value in Sheet2 it is 4523 (B1). I need to see if 4523 lies between B1 & C1 in Sheet1 (that would be 3000 & 3099), which is not, so I go to the next row in Sheet2 (4500 & 4599) which is true, so I need to get the value from A2 in Sheet1 and put it in A1 in Sheet2 (as we are looking at value in B1) Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value between 2 values
Hi Bob,
I have this spreadsheet that I can send you, I do not see any place where I can attach it. If you could provide me with a email id I can send it to u. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Why does this code remove Duplicate Values, by showing only 1, but it does NOT show Unique values for some reason ? | Excel Programming | |||
How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in | Excel Programming | |||
Predict Y-values on new X-values based on other actual X and Y values? | Excel Programming |