Consolidate data from one sheet to another -How?

jsgeare

Board Regular
Joined
Dec 3, 2005
Messages
131
I have an order form of about 200 rows of product descriptions, prices and an empty cell for the customer to enter a quantity. I want to take the order form data and use it in an invoice which will list ONLY the products for which a qty was entered, all the rows together, no blank ones.

So far, the best I have come up with is to read each row with an IF statement that pulls the data from the order sheet to an invoice sheet IF qty > 0. But, where qty is not > 0, it leaves a blank line. And so, if the customer ordered the first item and the 200th, the invoice sheet will show the item on line 1, then 198 blank lines, then the 200th item. Otherwise, the products ordered are scattered up and down the invoice sheet.

I sort this list to push all the blank lines to the bottom, and there I have my invoice. But I suspect there is a more elegant way simply to bring the data across, without blank lines, from the get-go.

Any thoughts?

Thanks!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

KJLoomis

New Member
Joined
Dec 10, 2005
Messages
28
You could use and AutoFilter to hide all other lines that do not contain values that way only the items that on the invoice are shown.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,914
Messages
5,575,011
Members
412,634
Latest member
Sumanmathew
Top