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

[im2bz2p345]

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

 

[im2bz2p345]

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

*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