|
Brain teaser
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! .. |
Brain teaser
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 =--- |
Brain teaser
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 =--- . |
Brain teaser
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 =--- |
| All times are GMT +1. The time now is 03:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com