# Extract Information from Row but Skip Row if a Particular Column is Blank

#### daverosati1785

##### New Member
I'm trying to take information filled-out by customers on one Excel sheet (a purchase order) and have it automatically fill-out another spreadsheet (invoice). I'm trying to avoid having to manually fill-out customer invoices by instead linking the spreadsheets together. The issue I'm having is this:

The purchase order spreadsheet basically looks like this:
 Product Name​ Price Per Unit​ Quantity​ Widget A \$1.00 2 Widget B \$2.00 Widget C \$3.00 1

The customer fills-out the Quantity column for the products they want and leaves it blank for the products they don't. The above is an example.

In turn, in my invoice spreadsheet, I need to generate a similar set of columns that simply skip over the products the customer didn't select. So for instance, my invoice would just look like this:
 Product Name​ Price Per Unit​ Quantity​ Widget A \$1.00 2 Widget C \$3.00 1

I need it to pull the information from the first spreadsheet while skipping rows that had a blank cell in the Quantity column. I've been messing around with arrays, the index function, the ifblank function, etc. but can't seem to get it.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

##### Well-known Member
Hi DaveRosati1785,

Here's a PO sheet
DaveRosati1785.xlsx
ABC
1Product NamePrice Per UnitQuantity
2Widget A\$1.00 2
3Widget B\$2.00
4Widget C\$3.00 1
PO

Here's the Invoice sheet just pulling across those rows with positive purchase quantity
DaveRosati1785.xlsx
ABC
1Product NamePrice Per UnitQuantity
2Widget A\$1.00 2
3Widget C\$3.00 1
Invoice
Cell Formulas
RangeFormula
A2:C3A2=IFERROR(INDEX(PO!A\$2:A\$99,AGGREGATE(15,6,ROW(PO!\$A\$2:\$A\$99)-ROW(PO!\$A\$1)/((PO!\$C\$2:\$C\$99>0)),ROWS(\$A\$1:\$A1))),"")

#### daverosati1785

##### New Member
Thanks
I tried doing this but it didn't work. My actual PO has 25 different products that can be selected (i.e. rows). As a sample, I've inserted a quantity on 4 of them at random. When I tried using your formula in the invoice, it correctly detected that there are only 4 "hits" and therefore only populated the first 4 lines of my invoice, but it populated them with simply the first 4 available product rows from my PO, rather than the 4 particular product rows that had positive quantities inputted.

#### daverosati1785

##### New Member
Hi DaveRosati1785,

Here's a PO sheet
DaveRosati1785.xlsx
ABC
1Product NamePrice Per UnitQuantity
2Widget A\$1.00 2
3Widget B\$2.00
4Widget C\$3.00 1
PO

Here's the Invoice sheet just pulling across those rows with positive purchase quantity
DaveRosati1785.xlsx
ABC
1Product NamePrice Per UnitQuantity
2Widget A\$1.00 2
3Widget C\$3.00 1
Invoice
Cell Formulas
RangeFormula
A2:C3A2=IFERROR(INDEX(PO!A\$2:A\$99,AGGREGATE(15,6,ROW(PO!\$A\$2:\$A\$99)-ROW(PO!\$A\$1)/((PO!\$C\$2:\$C\$99>0)),ROWS(\$A\$1:\$A1))),"")
I tried doing this but it didn't work. My actual PO has 25 different products that can be selected (i.e. rows). As a sample, I've inserted a quantity on 4 of them at random. When I tried using your formula in the invoice, it correctly detected that there are only 4 "hits" and therefore only populated the first 4 lines of my invoice, but it populated them with simply the first 4 available product rows from my PO, rather than the 4 particular product rows that had positive quantities inputted.

##### Well-known Member

I don't know what to say as it's working fine for me. Here's an extended PO tab set of data

Here's the Invoice tab which picks up the ones with values in Quantity. The formula in A2 can just be copy & pasted across then down.

#### mikerickson

##### MrExcel MVP
You could use AutoFilter and then copy paste the rows to the invoice sheet.

#### daverosati1785

##### New Member
I don't know what to say as it's working fine for me. Here's an extended PO tab set of data
View attachment 14999

Here's the Invoice tab which picks up the ones with values in Quantity. The formula in A2 can just be copy & pasted across then down.
View attachment 15000
Okay I think I got it pretty close now! It's filtering and returning values correctly finally. However, it's pulling the values from the row immediately above the desired row from the PO. So for instance, if Widget A was left blank for Quantity and Widget B was selected, my Invoice is returning the info for Widget A instead of Widget B.

Obviously I'm screwing something up when I'm replacing the example cell references and ranges with my actual ones!

#### daverosati1785

##### New Member
Okay I think I got it pretty close now! It's filtering and returning values correctly finally. However, it's pulling the values from the row immediately above the desired row from the PO. So for instance, if Widget A was left blank for Quantity and Widget B was selected, my Invoice is returning the info for Widget A instead of Widget B.

Obviously I'm screwing something up when I'm replacing the example cell references and ranges with my actual ones!
Nevermind, I found the error, thank you so much for your help on this!!!

Replies
0
Views
36
Replies
5
Views
226
Replies
1
Views
130
Replies
3
Views
148
Replies
1
Views
340

1,127,212
Messages
5,623,420
Members
415,974
Latest member
ZorroOP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back