im2bz2p345
Board Regular
- Joined
- Mar 31, 2008
- Messages
- 226
Hi all,
I have a simple request that could probably easily be done via VBA, but unfortunately my skills are severely lacking in this area (besides using the record a macro).
A sample of my worksheet is posted below. What I need to happen is:
1) If there is match in FIN (COLUMN D)
2) Amongst the matches, look in COID (Column B) to see which one is NOT "25748."
3) Whichever one is NOT 25748, compare the PT CHARGE (Column E) and whichever is highest, copy that into appropriate cell in the FINAL CHARGE (Column F). The highest PT CHARGE would be placed in the cell exactly to the right of it basically.
Example: There are three FIN's with the number "359", so it would look at Column B (COID) and see that COID 39385 has the largest PT CHARGE value (it would need to compare the $6271 to the $6300, since the $6000 value is associated COID 25748 and that should be ignored). It would then copy the $6300 into Cell F7 and move onto the next FIN.
4) Exception: If there is no FIN match, then copy the PT CHARGE (Column E) directly to the right into the FINAL CHARGE (Column F). This would mean that "25748" is the only COID, as with FIN 8 in the row 4 of my sample below.
5) Note: For a given FIN, if all the PT Charges are the same, then there won't be a max value so just copy the first PT CHARGE into the FINAL CHARGE column. You can see an example of this with FIN 369 below.
TEST
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Let me know if any further clarification is needed.
Thanks in advance for any help that you can provide!!
~ Im2bz2p345
I have a simple request that could probably easily be done via VBA, but unfortunately my skills are severely lacking in this area (besides using the record a macro).
A sample of my worksheet is posted below. What I need to happen is:
1) If there is match in FIN (COLUMN D)
2) Amongst the matches, look in COID (Column B) to see which one is NOT "25748."
3) Whichever one is NOT 25748, compare the PT CHARGE (Column E) and whichever is highest, copy that into appropriate cell in the FINAL CHARGE (Column F). The highest PT CHARGE would be placed in the cell exactly to the right of it basically.
Example: There are three FIN's with the number "359", so it would look at Column B (COID) and see that COID 39385 has the largest PT CHARGE value (it would need to compare the $6271 to the $6300, since the $6000 value is associated COID 25748 and that should be ignored). It would then copy the $6300 into Cell F7 and move onto the next FIN.
4) Exception: If there is no FIN match, then copy the PT CHARGE (Column E) directly to the right into the FINAL CHARGE (Column F). This would mean that "25748" is the only COID, as with FIN 8 in the row 4 of my sample below.
5) Note: For a given FIN, if all the PT Charges are the same, then there won't be a max value so just copy the first PT CHARGE into the FINAL CHARGE column. You can see an example of this with FIN 369 below.
TEST
* | A | B | C | D | E | F |
1 | Key | COID | LIUOM Unit Price Amt | FIN | PT CHARGE | FINAL CHARGE |
2 | 25748000000003 | 25748 | $170.500 | 3 | $ 1,073.00 | * |
3 | 39385000000003 | 39385 | $170.500 | 3 | $ 1,000.00 | * |
4 | 25748000000008 | 25748 | $78.000 | 8 | $ * 555.00 | * |
5 | 03132000000359 | 03132 | $1,347.420 | 359 | $ 6,271.00 | * |
6 | 25748000000359 | 25748 | $1,347.420 | 359 | $ 6,000.00 | * |
7 | 39385000000359 | 39385 | $1,347.420 | 359 | $ 6,300.00 | * |
8 | 25748000000369 | 25748 | $2,895.000 | 369 | $11,780.00 | * |
9 | 34385000000369 | 34385 | $2,895.000 | 369 | $11,780.00 | * |
10 | 34392000000369 | 34392 | $2,895.000 | 369 | $11,780.00 | * |
11 | 39385000000369 | 39385 | $2,895.000 | 369 | $11,780.00 | * |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Let me know if any further clarification is needed.
Thanks in advance for any help that you can provide!!
~ Im2bz2p345
Last edited: