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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
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?
 

lloyd1

New Member
Joined
Dec 23, 2017
Messages
8
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,724
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

lloyd1

New Member
Joined
Dec 23, 2017
Messages
8
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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,724
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback and welcome to the forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,396
Messages
5,601,414
Members
414,449
Latest member
Pashtun

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