PDA

View Full Version : Residual value next row



Cass
03-31-2006, 11:11 AM
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 :yes
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
:help

lucas
03-31-2006, 11:18 AM
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.

Bob Phillips
03-31-2006, 11:30 AM
C1: =MIN(VLOOKUP(A1,table2,2),B1)
C2: =MIN(VLOOKUP(A2,table2,2)-C1,B2)

Cass
03-31-2006, 11:52 PM
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

lucas
04-01-2006, 11:01 AM
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.

Cass
04-04-2006, 04:52 AM
maybe some kind of vba loop over all table1. Or this is very stupid idea and slow

jindon
04-05-2006, 02:14 AM
Hi
paste the code onto sheet module

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

Cass
04-05-2006, 07:29 AM
Ok but i dont understund yet how it works :):confused:

jindon
04-05-2006, 05:15 PM
it works when you change either col.A or col.B...

Cass
04-21-2006, 05:39 AM
Ok it works but real requirement is more complicated.
I try prepare better example

Cass
04-23-2006, 10:50 PM
Hi
paste the code onto sheet module

How can i use it in Module and stand alone code :help
And give the variable range and data :dunno

--Edit--
Here are the new sample :yes
Look there 'FING' and 'Must be' column

jindon
04-24-2006, 06:25 PM
Ok it works but real requirement is more complicated.
I try prepare better example
I don't understand why these things happen so many times...
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 our time, do we?
Please cralify the problem again

How can i use it in Module and stand alone code :help
And give the variable range and data :dunno

Cass
04-28-2006, 09:35 AM
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. :think:

jindon
04-28-2006, 05:38 PM
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.