Print table after splitting it in Excel

Daniele Brunengo
Daniele Brunengo used Ask the Experts™
on
Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here's an array formula that will give you the results you're looking for. See attached workbook.

=IFERROR(IF(A1="","",INDIRECT("A"&SMALL(IF($B$1:$B$10="AA",ROW($A$1:$A$10)),ROW(1:1)))),"")

Open in new window


The above is the formula found in cell E1 of the attached. In short, from "Small" to the end looks at column B of the data source for "AA" and ROW(1:1) gives you the first row number that AA appears in. In your example it's row 1. Adding Indirect("A"& is just like entering =A1.  As you copy the formula down you will see that the only thing that changes is ROW(, from 1:1 to 2:2 and so on. ROW(2:2) will give you the row number of the second instance of AA. In cell F1 the only change to the formula is INDIRECT("B", which will give you the result found in column B. Let me know if you have any questions.

As this is an array formula, be sure to enter it using Ctrl+Shift+Enter.

Paul
IndirectandSmall.xlsx
Daniele BrunengoIT Consultant, Web Designer

Author

Commented:
Thanks, it's a great job but it's not what I meant. The guys want the table to stay as it is on screen, but be printed on paper as if it was 3 (in the example) different tables on different pages.
Daniele BrunengoIT Consultant, Web Designer

Author

Commented:
Or maybe a macro that creates each table in a different sheet and then separately prints each sheet?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You can apply a filter to the second column and then print the filtered data. Would that work?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
To print a table on it's individual page, you may try the following macro.
In the attached, click the button called "Print Tables" on Sheet1 to run the macro.

Sub PrintTables()
Dim wsData  As Worksheet
Dim wsTemp  As Worksheet
Dim i       As Long
Dim j       As Long
Dim x       As Variant
Dim y()     As Variant
Dim dict    As Object
Dim it      As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wsData = Worksheets("Sheet1")   'Sheet with Data

On Error Resume Next
Set wsTemp = Worksheets("Temp")
wsTemp.Delete
On Error GoTo 0

Set wsTemp = Worksheets.Add
wsTemp.Name = "Temp"

Set dict = CreateObject("Scripting.Dictionary")
x = wsData.Range("A1").CurrentRegion.Value

For i = 1 To UBound(x, 1)
    dict.Item(x(i, 2)) = ""
Next i



For Each it In dict.keys
    ReDim y(1 To UBound(x, 1), 1 To 3)
    j = 0
    For i = 1 To UBound(x, 1)
        If x(i, 2) = it Then
            j = j + 1
            y(j, 1) = x(i, 1)
            y(j, 2) = x(i, 2)
            y(j, 3) = x(i, 3)
        End If
    Next i
    wsTemp.Cells.Clear
    wsTemp.Range("A1").Resize(j, 3).Value = y
    wsTemp.PrintOut
    Erase y
Next it
wsTemp.Delete
Application.ScreenUpdating = True
End Sub

Open in new window

Print-Tables.xlsm
Daniele BrunengoIT Consultant, Web Designer

Author

Commented:
Thanks guys. Subodh, this is perfect. I'll adapt it to the original spreadsheet, it works great.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Daniele! Glad it worked as desired.
Thanks for the feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial