Hello! I need help in figuring out which formula I need to use in order to extract data from a range of cells that contain $ amounts and capture ONLY those cells in a separate list.

Here's an example of what I'm working with...

DATA SET:

A1 - Unit 123 B1 - $35.00

A2 - Unit 456 B2 - (blank)

A3 - Unit 789 B3 - $159.00

A4 - Unit 1011 B4 - $4500.00

A5 - Unit 1213 B5 - (blank)Results:

Unit 123 - $35.00

Unit 789 - $159.00

Unit 1011 - $4500.00Thanks for your help!

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.

Let us know if that is an option and we can offer some suggestions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

A1 Contains the Unit Number, B1 contains the dollar amount so the data is split into separate columns. A B

1 Unit 123 $35.00

2 Unit 456

3 Unit 789 $159.00

4 Unit 1011 $4,500.00

5 Unit 1213

message edited by Hollis17

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.A B C D 1 Unit Amount =IFERROR(INDEX($A$1:$B$6,MATCH(D1,$B$1:$B$5,0),1),"") =IFERROR(INDEX($B$1:$B$6,SMALL(IF($B$1:$B$6<>"",ROW($B$1:$B$6)),ROW(1:1))),"") 2 Unit 123 $35.00 3 Unit 456 4 Unit 789 $159.00 5 Unit 1011 $4500.00 6 Unit 1213

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History