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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
You could use AutoFilter and then copy paste the rows to the invoice sheet.
 

daverosati1785

New Member
Joined
May 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,842
Messages
5,627,203
Members
416,229
Latest member
mohammadmihdi

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
Top