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.

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??

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.

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?

the screenshots!!

i kind of think without VBA/Macro the problem is not going to solve. tell me if i m wrong.

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.

hey, bostongirl!

exactly!

but i never worked with macros before. was trying to write a macro but it did not work.

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

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.

here is the vba module

hyaa... Good work!
:)

hyaaaaaaaaaaaaa!!!!

gr8! thanks bro.

no worries bro! i'm glad i was able to help!!! ah well that was little too much. anyways good luck with your studies!!!

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..??

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)

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.

yeo.. oz bro..
could you plz send me the book at bond17_007@hotmail.com

thanks,

i use excel help files. or msdn online.

Is there a single formula in cell B2 which can be copied across and down to create a multiplication table? (Hint: Think 'absolutely')

Here, eat this:

=row()*column()

copy everywhere.