|
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! |
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! |
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 |
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 |
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 |
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 |
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, |
Value between 2 values
Hi Bob,
Never mind here I have copied the data Hi, Thanks for the reply. I also used the same thing, but did not work for some cells. Here is the test data that 'm using on Sheet1 ABCDE 3000 3099 BDSCXD 4500 4599 UIT 4800 5199 SWEDFE 5300 5399 SmartS 5450 5499 XYZ123 5600 5629 QWER23 5650 5669 342222 5800 5899 Test123 5900 6330 1223324 6420 6499 65435 6360 6419 wqewwq 6700 6999 fgf 9101 9195 retreg 9201 9219 erw34def 9319 9331 Data on Sheet2 after applying the formula QWER23 5663 342222 5830 342222 5830 342222 5831 342222 5838 Test123 6005 Test123 6399 Test123 6399 Test123 6399 Test123 6399 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6404 Test123 6404 Test123 6404 Test123 6404 Test123 6404 65435 6429 65435 6429 wqewwq 6751 So where u see 6429, I should have had "1223324" instead of "65435", Similar for 6404, it should have been "65435" instead of "Test123". Any Suggesstion, why I get wrong results Thanks |
Value between 2 values
The data on sheet1 has to be ordered, as you showed in your original example
for it to work.So sort on column B. If the data cannot be ordered, this array formula works =INDEX(Sheet1!$A$1:$A$200,MATCH(1,(B1=Sheet1!$B$1 :$B$200)*(B1<=Sheet1!$C$1:$C$200),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hi Bob, Never mind here I have copied the data Hi, Thanks for the reply. I also used the same thing, but did not work for some cells. Here is the test data that 'm using on Sheet1 ABCDE 3000 3099 BDSCXD 4500 4599 UIT 4800 5199 SWEDFE 5300 5399 SmartS 5450 5499 XYZ123 5600 5629 QWER23 5650 5669 342222 5800 5899 Test123 5900 6330 1223324 6420 6499 65435 6360 6419 wqewwq 6700 6999 fgf 9101 9195 retreg 9201 9219 erw34def 9319 9331 Data on Sheet2 after applying the formula QWER23 5663 342222 5830 342222 5830 342222 5831 342222 5838 Test123 6005 Test123 6399 Test123 6399 Test123 6399 Test123 6399 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6400 Test123 6404 Test123 6404 Test123 6404 Test123 6404 Test123 6404 65435 6429 65435 6429 wqewwq 6751 So where u see 6429, I should have had "1223324" instead of "65435", Similar for 6404, it should have been "65435" instead of "Test123". Any Suggesstion, why I get wrong results Thanks |
| All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com