Does A1 contain the entire string or are the dollar amounts in a separate column?

It would be so much easier if the dollar amounts are in a separate column. If they are not, the strings in Column A could be split relatively easily, i.e. they would not need to be re-entered manually.

Add Headers in Row 1 and use the formulas shown. The formula in D1 is an Array formula that must be entered with Ctrl-Alt-Enter before it is dragged downward. Braces { } will appear around the formula to indicate that it is an array formula.

Sorry for the need to scroll but the formulas are kind of long. Copy/paste is your best option.

The Array formula in Column D is the formula that creates the list of non-blank "Amount" entries. The formula in Column C is nothing more than an alternate version of VLOOKUP that can look to the left and pull in the unit number associated with each "Amount".

The IFERROR function is used so that you can drag the formulas down as far as you'll ever possibly need (Row 6 in this example) and not have errors appear when there aren't enough entries to fill the entire range.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.