Hello!
I have for me big question how resolve this:
Large table were is some same entry
now vlookup another table value for first table row. but if first table is 2 same entry then i need put residual value this second row.
uh my english
but example:
table1:
entry1, 20 (A1:B1)
entry1, 30 (A2:B2)
tbale2:
entry1, 50
now the formula lookup table2 the value and put first 20 to table1!C1 and remaining to table1!C2
Last edited by Cass; 03-31-2006 at 11:50 PM.
Cass, your English is a lot better than any of my alternate language skills. It would help if you could upload an example workbook with all sensitive material removed(also check document properties). Sometimes it helps to see what the problem is and we can go from there.
click on post reply and scroll down to manage attachments to attach your file.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
C1: =MIN(VLOOKUP(A1,table2,2),B1)
C2: =MIN(VLOOKUP(A2,table2,2)-C1,B2)
Ok i make a simple example workbook (first post)
Bottom table is right result there
nb! the real table is much largest than this example
Sorry Cass,
I was not smart enough to come up with a clean solution but I do have a workaround that you can examine in the attachment. I hope Bob or one of the other experts come along to give you better help.
The only way I could figure out how to do what you wanted was to filter first then copy the filtered data, then use formula's on the copied data.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
maybe some kind of vba loop over all table1. Or this is very stupid idea and slow
Hi
paste the code onto sheet module
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, dic As Object
With Target.Cells(1, 1)
If Intersect(.Cells, Range("a2", Range("a" & Rows.Count)) _
.Resize(, 2)) Is Nothing Then Exit Sub
If IsEmpty(Cells(.Row, "a")) Then Exit Sub
If Not IsNumeric(Cells(.Row, "b")) Then
MsgBox "Invalid entry"
.ClearContents
.Activate
Exit Sub
End If
End With
Application.EnableEvents = False
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
a = Range("h1").CurrentRegion.Resize(, 2).Value
For i = 2 To UBound(a, 1)
If Not dic.exists(a(i, 1)) Then
dic.Add a(i, 1), a(i, 2)
End If
Next
a = Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 3).Value
For i = 1 To UBound(a, 1)
If dic(a(i, 1)) > 0 Then
On Error Resume Next
x = dic(a(i, 1)) - a(i, 2)
If Err.Number <> 0 Then
MsgBox "Found invalid entry in " & Cells(i + 1, 2).Address(0, 0)
GoTo Last
End If
If x > 0 Then
a(i, 3) = a(i, 2)
dic(a(i, 1)) = x
Else
a(i, 3) = dic(a(i, 1))
dic(a(i, 1)) = 0
End If
Else
a(i, 3) = 0
End If
Next
Range("a2").Resize(UBound(a, 1), 3) = a
Last:
Erase a: Set dic = Nothing
Application.EnableEvents = True
End Sub[/vba]
Last edited by jindon; 04-05-2006 at 02:25 AM.
Ok but i dont understund yet how it works
it works when you change either col.A or col.B...
Ok it works but real requirement is more complicated.
I try prepare better example
How can i use it in Module and stand alone codeOriginally Posted by jindon
And give the variable range and data
--Edit--
Here are the new sample
Look there 'FING' and 'Must be' column
Last edited by Cass; 04-24-2006 at 06:35 AM.
I don't understand why these things happen so many times...Ok it works but real requirement is more complicated.
I try prepare better example
Please submit the sample data with
1) Same sheet structure as original
2) Same data type for each field as original
It doesn't really matter if the data is not as your original, but for the above items.
As VBA is not so flexible like formula(it will be fuge code, if I make it so), it is hard to adjust it when it is complicated and if you can, you've already done it for youself before you ask.
We don't want to waste ouｒ time, do we?
Please cralify the problem again
How can i use it in Module and stand alone code
And give the variable range and data
Last edited by jindon; 04-24-2006 at 08:46 PM.
Sorry, for that confuse.
I tried modify your code for the example file test2 but it's to hard for me.
First file was just example what the code must be do.
OK
Don't get me wrong, what I was trying to say is that
1) 1st priority shoud be to solve your current problem
2) If you want to learn something from the code, you can do it from the code that is running as you expected. and take time to understand it.
3) if you think you can adjust from the sample data with your actual data, don't even try to think that way.
Because, the code may be totally different from the sample code.
if the data and the layout is close enough to the actual data, it will be slight adjustment can be applied, but if it is not...... just waisting time..
Please post a new file with before/after thingy... because I don't fully understand what you are after now.
Posting Permissions
Reply With Quote
