Trying to create a summary sheet with only rows that contain data

lloyd1

New Member
Joined
Dec 23, 2017
Messages
8
Hi,

I'm trying to create a summary sheet of a form with only the rows that contain data. We have a sheet with an order form for different parts pre-listed in each row (i.e. Row 1 is a Hammer, Row 2 is a Nail, etc. going down a few hundred rows). When an order is placed we indicate quantity in the appropriate row. Instead of printing all of the pages, ideally I'd like to create a summary report on a different sheet just showing the rows that have a quantity data.

Is there a formula that could do this? I'm familiar with sorting by column, perhaps pivot table or even a macro, but if there is a formula for this that would be ideal.

Thank you in advance for you help,
Lloyd
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I can't offer you a formula, but if you can answer the following, I can supply a macro.
1) What column contains the qty?
2) Where is your data? Header in Row 1, with data starting in A2?
3) What are the sheet names?
4) Are you entering the the quantities manually, or by formula?
 
Upvote 0
Thank you. Below I've responded in CAPS.

I can't offer you a formula, but if you can answer the following, I can supply a macro.
1) What column contains the qty? COLUMN G
2) Where is your data? Header in Row 1, with data starting in A2? YES, HEADER IN ROW 1, WITH DATA STARTING IN A2
3) What are the sheet names? SHEET NAMES: Page 1A, Page 1B, Page 2
4) Are you entering the the quantities manually, or by formula?
MANUALLY

Thank you.
 
Upvote 0
OK try this,it will copy each row with a qty from Page1A & Page1B to Page 2
Code:
Sub CopyQty()

   Dim Ws As Worksheet
   
   For Each Ws In Sheets(Array("Page1A", "Page1B"))
      With Ws.Range("G2", Ws.Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
         .EntireRow.Copy Sheets("Page2").Range("A" & Rows.Count).End(xlUp).Offset(1)
      End With
   Next Ws

End Sub
 
Upvote 0
Here is a formula way:

The formula in A2 is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down the column as needed.


Excel Workbook
AB
1ProductQty
2Prod112
3Prod44
4Prod72
5Prod105
6Prod111
7
Sheet2
Excel Workbook
ABFG
1ProductQty
2Prod112
3Prod2
4Prod3
5Prod44
6Prod5
7Prod6
8Prod72
9Prod8
10Prod9
11Prod105
12Prod111
13Prod12
14Prod13
Sheet1
 
Upvote 0
Thank you very much!!

Here is a formula way:

The formula in A2 is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down the column as needed.


Sheet2

AB
1ProductQty
2Prod112
3Prod44
4Prod72
5Prod105
6Prod111
7

<colgroup><col style="width:30px; "><col style="width:75px;"><col style="width:85px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A2{=IF(ROWS($A$2:A2)>COUNTIF(Sheet1!$G$2:$G$14,"<>"&""),"",INDEX(Sheet1!$A$2:$A$14,SMALL(IF(Sheet1!$G$2:$G$14<>"",ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1),ROWS($A$2:A2))))}
B2=IF(A2="","",INDEX(Sheet1!$G$2:$G$14,MATCH(A2,Sheet1!$A$2:$A$14,0)))

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Sheet1

ABFG
1Product Qty
2Prod1 12
3Prod2
4Prod3
5Prod4 4
6Prod5
7Prod6
8Prod7 2
9Prod8
10Prod9
11Prod10 5
12Prod11 1
13Prod12
14Prod13

<colgroup><col style="width:30px; "><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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