autopopulate formula from two columns of data

**thedunna** · 03-08-2013, 03:42 PM

greetings all,

I am hoping someone can provide a relatively novice user a simple formula. My project is:

In excel 2010, I have 2 columns (A & B). A data has already been entered, and is a list of all groceries. B column is blank until I enter the price next to the grocery I purchase. I would like to create another sheet I will call results, and would like it to autopopulate only columns A & B that have the price entered in column B, without listing the entire grocery list of items.

The grocery list thing is just an analogy, but I could really use some help. thanks...

Jim

**etaf** · 03-08-2013, 05:27 PM

try this in cell D2
=INDEX($A$2:$A$11, SMALL(IF(($B$2:$B$11>0), MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11))), ROW(A1)), COLUMN(A1))
and then in the next column to get the price
=VLOOKUP(D2,$A$2:$B$11,2,FALSE)

see attached

**thedunna** · 03-08-2013, 06:04 PM

If I have sent this several times, I apologize...I'm new. The formula works however is there a way to make the #NUM! go away? Can that just return a blank cell?

**etaf** · 03-08-2013, 06:15 PM

=IF(ISERR(INDEX($A$2:$A$11, SMALL(IF(($B$2:$B$11>0), MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11))), ROW(A1)), COLUMN(A1))),"",INDEX($A$2:$A$11, SMALL(IF(($B$2:$B$11>0), MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11))), ROW(A1)), COLUMN(A1)))
and
=IF(D2="","",VLOOKUP(D2,$A$2:$B$11,2,FALSE))

**Jacc** · 03-08-2013, 06:28 PM

Is this what you are looking for?

**thedunna** · 03-08-2013, 07:58 PM

Copy3 of AMC 2013 budget.xls

Thank you for your help. I thought I would be smart enough to adapt a working formula to my actual project, but I can't. I have attached a copy of my actual project. As you can see, tab 1 is "Budget" and tab 2 is "JAN-Report".

I am trying to populate the fields in JAN-Report (A14:A17) for description, and along with the correspondig amount in (F14:F17) for the amount. All of this is taken from Budget C6:C16 (for JAN). When an amount is entered into the C column it will automatically fill in JAN-Report with description and amount. Then I should be able to adapt it to the expense columns. I just need a start!

Thanks again...

Jim

**etaf** · 03-11-2013, 09:57 AM

C6:C16 does not match the number of rows in A14:A17 - so if you happen to have all rows complete with no blanks - that maybe an issue - so you may need a macro that inserts rows (not my expertise)
using Jacc formula

if you put in Jan report sheet cell A14
=IFERROR(INDEX(Budget!$A$6:$A$16,SMALL(IF(Budget!$C$6:$C$16<>"",ROW(Budget!$C$6:$C$16)-ROW($A$5)),ROWS($B$6:B6))),"-")
and use control+shift+enter {}
BUT i dont think you can put array formulas in merged cells - at least I cant in start2010

see attached - need to put an errorcondition for a 0 in the formula - is this what you are after ?