Solved How to populate a list of cells from a range of data

June 6, 2018 at 08:42:24
Specs: Windows 10
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.00

Thanks for your help!


See More: How to populate a list of cells from a range of data

Reply ↓  Report •

#1
June 6, 2018 at 09:01:07
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


Reply ↓  Report •

#2
June 6, 2018 at 09:41:45
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


Reply ↓  Report •

#3
June 6, 2018 at 10:19:55
✔ Best Answer
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


Reply ↓  Report •
Related Solutions


Ask Question