Excel Formula Help PLEASE 1

[PeanutB7]

Good Snowing Morning Folks,

I am in need of a complicated formula for an excel worksheet. First off I need to scan a column of ID numbers that are sorted in ascending order in column A. For example 1234000, 1234001, 1234002, 1235000, 1235001, 1236000, 1237000, etc. If the ID number in column/row A2 ends in 00 and is preceded by an 01 in column/row A3 it indicates a revision to the record identified by the ID number ending in 00. I need to ignore the respective dollar total in column/row B2 for the 00 and only pickup the 01, multiple it by .53 and post to an input column C in the respective row. If the ID in Column/row A2 ending in 00 is preceeded by an 01 in column/row A3 and then an 02 in column/row A4 I need to ignore 00 and 01 and only pickup the dollar total in column/row B4 respective to the row reflecting ID 02, multiple it by .53, post it to column/row C4 in the respective row and ignore the two predecessors and so on....

I have this formula which functions well to this extent:
=IF(LEFT(A2,10)=LEFT(A3,10),"",B2)


My next layer of complexitiy however is that if the ID number in column/row A2 is not followed by a revised ID number 01 in column/row A3 then there should be nothing posted to the column/row B2.

In essence we are trying only to pickup the totals on the revised records only as indicated by the ID number ending in 00 which is changed to 01 or 02 or 03, etc... and multiple them by .53

Could some excel super hero PLEASE help me with this missing link to my requirement. We can two step the process if required by posting all respective totals to column C and then filtering out the records that did not need revised in column D.

A B C
ID Fee Fee*.52
1234000 200
1234001 300
1234002 300 156
1235000 100
1235001 200 104
1236000 400
1237000 600
1237001 400
1237002 400
1237003 500 260
1238000 800



As always thank you so much for the anticipated wisdom,

JB

 

[SkipVought]

hi,
[tt]
=IF(AND(LEFT(A2,5)<>LEFT(A3,5),LEFT(A2,5)=LEFT(A1,5)),B2,"")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PeanutB7]

Skip - Thank you Sooooo much it works like a charm. What reference book or site would you recommend for these type of formulas? I have never found anythting in the Microsoft Help catalogues to answer these type of needs. Thank heaven for folks like yourself who are willing to share their coveted knowledge. I TRULY appreciate the input.

Thank you kindly,

JB