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

daverosati1785

New Member
Joined
May 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.002
Widget B$2.00
Widget C$3.001

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.002
Widget C$3.001

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.

Please help!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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))),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I don't know what to say as it's working fine for me. Here's an extended PO tab set of data
1590705480955.png


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.
1590705582110.png
 
Upvote 0
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!
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top