[VIEWED 10030
TIMES]
|
SAVE! for ease of future access.
|
|
The postings in this thread span 2 pages, go to PAGE 1.
This page is only showing last 20 replies
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 2:42
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
MS EXCEL experts out there, I need help!!!!! For example, the following is the original table: From/To indicates the range for the "value" Item From To Value 200000002 0.000 0.189 3423 200000002 0.189 0.830 3424 200000002 0.830 3.943 3433 200000002 3.943 4.393 865 200000002 4.393 5.607 3454 200000008 0.000 1.278 8889 200000008 1.278 5.432 5587 200000010 21.625 21.812 4564 200000010 21.812 22.468 4777 200000010 22.468 24.624 4556 200000012 27.068 41.089 6689 200000012 41.089 42.007 7688 I want correct values in the "Value" column in the following table: Item Location Value 200000002 0.261 (the right value is 3424) 200000008 0.5 ??? 200000008 3.698 ??? 200000008 4.32 ??? 200000008 15.396 ??? 200000008 15.757 ??? 200000008 16.417 ??? How do I get the right values in the "Value" column. I used VLOOKUP function of Excel but the problem is that VLOOKUP only looks at the first match in the "Item" field. How do I make it to look at the next row when the first match does not meet the criteria? I tried VBA/Macro but my knowledge is limited in these fields. Helps will be appreciated.
|
|
|
The postings in this thread span 2 pages, go to PAGE 1.
This page is only showing last 20 replies
|
|
bostongirl
Please log in to subscribe to bostongirl's postings.
Posted on 10-09-06 3:50
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Hmmm... is this Shanka fella gonna let us pick our brain and vanish??? From his posting, it looked like he needed urgent help...now we are all ready to go, but he is gone!! What happened to respecting other people's time??
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 4:08
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
hey, bhusan: I guess, I made my question a lil complicated. actually, i want the correct value in the second table. e.g., the value in the first row in the second table should be 3424 (by observation) as location 0.261 is in the range of 0.189 to 0.830 in the first column, right? similarly, the value in the last row at location 16.417 against Item 200000008 is none as this location is beyond the range of 0 to 1.278 and 1.278 to 5.432 against Item 200000008 in the first table. i have thousands of data. so the question is how do i make excel to do it for me, ie, return me the right value at a location of an item as specified by the range. hey, bostongirl: Yes the location is not the same. these are the given locations at which i need the correct values from first table in which the values are specified by the range. clear?? hey, Slackdemic: I will try to include the screenshots! hey, TM: Yes. thats true. but thats how i got the data - thousands of entry. i guess we will have to ignore that part. GUYS!!! LEMME KNOW IF U STILL VE QUESTIONS.
|
|
|
bostongirl
Please log in to subscribe to bostongirl's postings.
Posted on 10-09-06 4:18
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
value in the first row in the second table should be 3424 (by observation) as location 0.261 is in the range of 0.189 to 0.830 in the first column, right? No, how can 0.261 fall in the range of 0.189 to 0.830?
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 4:22
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
the screenshots!!
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 4:29
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
i kind of think without VBA/Macro the problem is not going to solve. tell me if i m wrong.
|
|
|
bostongirl
Please log in to subscribe to bostongirl's postings.
Posted on 10-09-06 4:32
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
I doubt Vlookup is going to be of any help in this. I tried couple of combinations and it doesnt give you the right 'value' because its not capable of recognizing where the 'location' falls in between the ranges. You might want to go through macros in this one.
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 4:44
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
hey, bostongirl! exactly! but i never worked with macros before. was trying to write a macro but it did not work.
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-09-06 4:51
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
hey, bostongirl! can you tell me whats wrong with following. it did not work. Sub try() ' ' route1 is the table_array. Do If IsEmpty(ActiveCell) Then If ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>=VLOOKUP(RC[-2],route1,2,0),RC[-1]<=VLOOKUP(RC[-2],route1,3,0)),VLOOKUP(RC[-2],route1,5,0),""Error!!!"")" = "Error!!!" Then route1.Range (x + 1) Else ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>=VLOOKUP(RC[-2],route1,2,0),RC[-1]<=VLOOKUP(RC[-2],route1,3,0)),VLOOKUP(RC[-2],route1,5,0),""Error!!!"")" End If End If ActiveCell.Offset(1, 0).Select Loop Until ActiveCell = "g" End Sub
|
|
|
hyaaaaaaaaaaaaa
Please log in to subscribe to hyaaaaaaaaaaaaa's postings.
Posted on 10-10-06 12:51
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
here is your macro bro! Sub test() For i = 2 To 13 ' number of data rows in input array For j = 2 To 8 ' number of data rows in output array If Cells(j, 7) = Cells(i, 1) And Cells(j, 8) >= Cells(i, 2) And Cells(j, 8) <= Cells(i, 3) Then Cells(j, 9) = Cells(i, 4) End If Next j Next i End Sub NOTE: you have to play with i and j values. this module works for the number you have put up in your first post. i'm attaching my excel snapshot. i guess you can go from there. if not email me and i can help. you have to change i, j and column values in IF statement as per the location of your tables. my best advice is try to match your spreadsheet as how i did. :) hope this helps.
|
|
|
hyaaaaaaaaaaaaa
Please log in to subscribe to hyaaaaaaaaaaaaa's postings.
Posted on 10-10-06 12:52
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
here is the vba module
|
|
|
bond_oo7
Please log in to subscribe to bond_oo7's postings.
Posted on 10-10-06 8:52
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
shankha
Please log in to subscribe to shankha's postings.
Posted on 10-10-06 9:23
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
hyaaaaaaaaaaaaa!!!! gr8! thanks bro.
|
|
|
hyaaaaaaaaaaaaa
Please log in to subscribe to hyaaaaaaaaaaaaa's postings.
Posted on 10-10-06 10:32
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
no worries bro! i'm glad i was able to help!!! ah well that was little too much. anyways good luck with your studies!!!
|
|
|
bond_oo7
Please log in to subscribe to bond_oo7's postings.
Posted on 10-11-06 9:22
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Hey.. HYAAAAAAA bro! is there a place where you can learn more about macro programming? i use excel for 95% of work.. I have to all shots of thing in excel.. i have been going through online help and stuff to learn more about differnt functions and stuff.. i am good in java.. and have moderate programming skills in vb.. if you could share that .. this brother would be glad as my work would be lot easier... some of the things i do are.. doing matchs.. getting file content from differnt system to analyze certain fields. (this is often hard.. coz every line has differnt format and I will have to work around a lot to use advance filter... ) any suggestions..??
|
|
|
oz
Please log in to subscribe to oz's postings.
Posted on 10-11-06 4:06
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
007 bro, if you know vb . . . that's it (forget java for a while) Open Excel. Press ALT + F11 Start coding. I've got an ebook on that . . . Programming Excel with VBA and .NET By Steve Saunders, Jeff Webb Publisher - O'Reilly if you want it send me your email address (its about 9MB)
|
|
|
rajeshHamal
Please log in to subscribe to rajeshHamal's postings.
Posted on 10-11-06 6:55
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
I use "Excel 97 programming for dummies" John walkenbach ISBN 0-7645-0132-1 Life has been a lot easier. I use VBA to send data back and forth between AutoCAD & Excel. Once you learn the basics, you can utilize codes available online to be tailored for your individual use.
|
|
|
bond_oo7
Please log in to subscribe to bond_oo7's postings.
Posted on 10-11-06 11:00
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
hyaaaaaaaaaaaaa
Please log in to subscribe to hyaaaaaaaaaaaaa's postings.
Posted on 10-11-06 11:34
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
i use excel help files. or msdn online.
|
|
|
raascal
Please log in to subscribe to raascal's postings.
Posted on 10-12-06 12:55
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Is there a single formula in cell B2 which can be copied across and down to create a multiplication table? (Hint: Think 'absolutely')
|
|
|
rajeshHamal
Please log in to subscribe to rajeshHamal's postings.
Posted on 10-12-06 2:17
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Here, eat this: =row()*column() copy everywhere.
|
|