Simple Macro for Comparison & Selecting Maximum Value - Help Needed!

im2bz2p345

Board Regular
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

 * 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:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

im2bz2p345

Board Regular
*small bump*

If anyone is still awake and could help me get started, that would be a huge deal! I am wanting to try out some code on a larger spreadsheet at work.

~ Im2bz2p345

im2bz2p345

Board Regular
*final bump before I try something else* Any help? Is there anyway to do this without VBA?

If you could give me some tips on a non-VBA solution, I could probably figure it out.

~ Im2bz2p345

1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back