List comparison and update of values

[Dakotasteve]

(Nigel gave me some great advice about using a data sort
solution to my question below, but it still requires a lot
of manual comparing of list to list. Is there any way to
automate this and have the computer do it??)


Hello out there!

Here's my goal. Whether tis possible remains an open
question. I have two lists, (each list can be on a
separate excel worksheet or separate file) one with
account numbers and amounts, sorted in order by the
mainframe. Same with the other list. What I would like
to do is compare the lists, update list A with List B
numbers or zero them out if they appear on list A but have
no amounts in list B.

List A List B

Fund Acct# Amount Fund Org # Amount
100 22001 $20.02 100 22001 $30.10
100 22002 30.00 100 22002 $40.24
200 24003 60.00 200 24001 31.00

So in row 2, the figure of $20.02 would be replaced by
$30.10, same situation in row 3, row 4 account 24003
should be zero because it has no value in the current list
B, and List A should be updated to insert a new account
24001 and the amount of $31.O0. I tried nested if
statements, but it was an entirely manual solution. I am
trying to automate. Any help would be great!


...

 

[Guest]

Hi

Assuming that your data is set out as below and is in the range A1:F4. You will have to modify any ranges as required.

1) Updating the list with new values
Put the formula
=SUMPRODUCT(--($D$2:$D$4=A2),--($E$2:$E$4=B2),($F$2:$F$4))
in cell C2 and copy down. This will update the A list with the results from the B list where there is a match, and put in a 0 where there is no match.

When done, copy the results in the range C2:C4 and value paste the results.

2)
When 1 is completed, run the macro below. It will append the new entries onto the bottom of the A list. When it is completed, you can then do a sort to reorder as required.

Sub aaa()
lastplace = Range("a1").End(xlDown).Offset(1, 0).Address
ctr = 0

Range("d2").Select
While Not IsEmpty(ActiveCell)
If Evaluate("=SumProduct(--(a2:a4 = " & ActiveCell & "), --(b2:b4 = " & ActiveCell.Offset(0, 1) & "))") = 0 Then
Range(lastplace).Offset(ctr, 0).Value = ActiveCell.Value
Range(lastplace).Offset(ctr, 1).Value = ActiveCell.Offset(0, 1).Value
Range(lastplace).Offset(ctr, 2).Value = ActiveCell.Offset(0, 2).Value
ctr = ctr + 1
End If
ActiveCell.Offset(1, 0).Select
Wend

End Sub

HTH

Tony


----- Dakotasteve wrote: -----

(Nigel gave me some great advice about using a data sort
solution to my question below, but it still requires a lot
of manual comparing of list to list. Is there any way to
automate this and have the computer do it??)


Hello out there!

Here's my goal. Whether tis possible remains an open
question. I have two lists, (each list can be on a
separate excel worksheet or separate file) one with
account numbers and amounts, sorted in order by the
mainframe. Same with the other list. What I would like
to do is compare the lists, update list A with List B
numbers or zero them out if they appear on list A but have
no amounts in list B.

List A List B

Fund Acct# Amount Fund Org # Amount
100 22001 $20.02 100 22001 $30.10
100 22002 30.00 100 22002 $40.24
200 24003 60.00 200 24001 31.00

So in row 2, the figure of $20.02 would be replaced by
$30.10, same situation in row 3, row 4 account 24003
should be zero because it has no value in the current list
B, and List A should be updated to insert a new account
24001 and the amount of $31.O0. I tried nested if
statements, but it was an entirely manual solution. I am
trying to automate. Any help would be great!


...

 

[dakotasteve]

Tony,
I can't thank you enough! I will try your solution when I
get into the office in the morning, but it looks like a
very elegant solution!!
Thanks again!
Steve C
in Los Gatos CA

-----Original Message-----
Hi

Assuming that your data is set out as below and is in the

range A1:F4. You will have to modify any ranges as
required.

1) Updating the list with new values
Put the formula
=SUMPRODUCT(--($D$2:$D$4=A2),--($E$2:$E$4=B2),($F$2:$F$4))
in cell C2 and copy down. This will update the A list

with the results from the B list where there is a match,
and put in a 0 where there is no match.

When done, copy the results in the range C2:C4 and value paste the results.

2)
When 1 is completed, run the macro below. It will append

the new entries onto the bottom of the A list. When it is
completed, you can then do a sort to reorder as required.

Sub aaa()
lastplace = Range("a1").End(xlDown).Offset(1, 0).Address
ctr = 0

Range("d2").Select
While Not IsEmpty(ActiveCell)
If Evaluate("=SumProduct(--(a2:a4 = " & ActiveCell & "), -

-(b2:b4 = " & ActiveCell.Offset(0, 1) & "))") = 0 Then