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

im2bz2p345

Board Regular
Joined
Mar 31, 2008
Messages
220
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

*ABCDEF
1KeyCOIDLIUOM Unit Price AmtFINPT CHARGEFINAL CHARGE
22574800000000325748$170.5003 $ 1,073.00*
33938500000000339385$170.5003 $ 1,000.00*
42574800000000825748$78.0008 $ * 555.00*
50313200000035903132$1,347.420359 $ 6,271.00*
62574800000035925748$1,347.420359 $ 6,000.00*
73938500000035939385$1,347.420359 $ 6,300.00*
82574800000036925748$2,895.000369 $11,780.00*
93438500000036934385$2,895.000369 $11,780.00*
103439200000036934392$2,895.000369 $11,780.00*
113938500000036939385$2,895.000369 $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
Joined
Mar 31, 2008
Messages
220
*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.

Please help,

~ Im2bz2p345 :)
 

im2bz2p345

Board Regular
Joined
Mar 31, 2008
Messages
220
*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 :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top