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!


..

I am presuming the following......
1. List A is your master list, that always shows the current values from
list B?
2 .List A should include new items in list B not in list A?
3. List A items with no matching B item should be set to 0?

Yuo need to confirm what the match key is - account # - fund or both?

One approach would be to combine the list into one list and sort in order of
the key(A) + list(D)
This way items appear in key sequence order with list B items appearing
before A, unless there is not a corresponding A item.
It should then be just a matter of scanning applying the rules listed above

1. Replace the value from the previous row where the key is the same.
2. Where there is no corresponding 'A' list item change the item that
follows to an 'A' type (puts it into the A list).
3. Where there is no corresponding B item reset the value to zero.

Now just delete items not with an A status and you have the final list.

"dakotasteve" wrote in message
...
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!


.






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Nigel,
Your assumptions about the requirements are correct! What
I didn't follow was the comment about list D. Can you
help figure out where list D comes about??
thx
Steve



-----Original Message-----
I am presuming the following......
1. List A is your master list, that always shows the
current values from
list B?
2 .List A should include new items in list B not in list
A?
3. List A items with no matching B item should be set to
0?

Yuo need to confirm what the match key is - account # -
fund or both?

One approach would be to combine the list into one list
and sort in order of
the key(A) + list(D)
This way items appear in key sequence order with list B
items appearing
before A, unless there is not a corresponding A item.
It should then be just a matter of scanning applying the
rules listed above

1. Replace the value from the previous row where the key
is the same.
2. Where there is no corresponding 'A' list item change
the item that
follows to an 'A' type (puts it into the A list).
3. Where there is no corresponding B item reset the value
to zero.

Now just delete items not with an A status and you have
the final list.

"dakotasteve" wrote in message
...
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!


.






----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.

Hi Steve,

Sorry List(D) is order Descending as opposed to (A) Ascending

Cheers
Nigel

"dakotasteve" wrote in message
...
Nigel,
Your assumptions about the requirements are correct! What
I didn't follow was the comment about list D. Can you
help figure out where list D comes about??
thx
Steve



-----Original Message-----
I am presuming the following......
1. List A is your master list, that always shows the
current values from
list B?
2 .List A should include new items in list B not in list
A?
3. List A items with no matching B item should be set to
0?

Yuo need to confirm what the match key is - account # -
fund or both?

One approach would be to combine the list into one list
and sort in order of
the key(A) + list(D)
This way items appear in key sequence order with list B
items appearing
before A, unless there is not a corresponding A item.
It should then be just a matter of scanning applying the
rules listed above

1. Replace the value from the previous row where the key
is the same.
2. Where there is no corresponding 'A' list item change
the item that
follows to an 'A' type (puts it into the A list).
3. Where there is no corresponding B item reset the value
to zero.

Now just delete items not with an A status and you have
the final list.

"dakotasteve" wrote in message
...
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!


.






----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---