rearrange colums and rows to save space when printing

[emma_jane]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]Incident Type
[/TD]
[TD]Written description
[/TD]
[TD]Action taken
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Emma
[/TD]
[TD]05/08/19
[/TD]
[TD]14:00
[/TD]
[TD]Environmental

[/TD]
[TD]I was walking across site and saw water gushing out of a pipe near the sheds at the back of the houses. Quite a large puddle had already deveoped and it looked like there could be risk of this spreading towards the houses. This was near where engineering works were taking place yesterday so this may have been a factor.
[/TD]
[TD]I contacted maintenance to alert them.
[/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I have a dataset for our company's incidents. I've given a brief example above. Most of the columns in my dataset are small; they contain short pieces of info such as name, location, category etc. However, 2 columns are large as they contain the written narrative of the incident and a description of all actions taken.

When I print out a list of the weeks incidents it looks terrible. For each row the two more columns containing the narrative go on for pages and the left hand of the table is mostly blank.

I'm looking for a better way to format this for printing. Ideally one record could be displayed across several rows to save space e.g. it would better if I could display 'time' and 'incident type' underneath 'name' and 'date'.

I could achieve this in word but that would involve some time consuming copy and pasting.

Any suggestions would be appreciated!

 

[MickG]

This is a bit unconventional , but it might work.
Try the following on your data sheet for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG05Aug56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] t
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Cells.Clear
    .Range("B:B").NumberFormat = "dd/mm/yyyy"
    .Range("C:C").NumberFormat = "hh:mm"
    .Cells.WrapText = False
    .Cells.MergeCells = False
    .Cells.RowHeight = 20
    c = 2
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 5
        [COLOR="Navy"]If[/COLOR] Ac < 4 [COLOR="Navy"]Then[/COLOR]
            .Cells(c - 1, Ac + 1) = Rng(1).Offset(-1, Ac)
            .Cells(c, Ac + 1) = Dn.Offset(, Ac)
        [COLOR="Navy"]Else[/COLOR]
            c = c + 2
            .Cells(c - 1, 1) = Rng(1).Offset(-1, Ac)
            .Cells(c, 1) = Dn.Offset(, Ac)
                [COLOR="Navy"]If[/COLOR] Len(Dn.Offset(, Ac)) > 100 [COLOR="Navy"]Then[/COLOR]
                    .Cells(c, 1).Resize(, 6).WrapText = True
                    .Cells(c, 1).Resize(, 6).MergeCells = True
                    .Cells(c, 1).RowHeight = Len(.Cells(c, 1)) / 4.5
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Ac
  c = c + 3
[COLOR="Navy"]Next[/COLOR] Dn
.Cells(1, 1).Resize(c - 3, 6).Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

NB:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.

Regards Mick

 

[emma_jane]

Oh wow!
That worked perfectly. Absolutely amazing. Thank you!
Could you, or anyone else here, recommend an online course or book so that I can begin to understand how you did that?
Thanks again!

 

[MickG]

Good news ,I'm pleased it worked for you.

Try the below from amazon its just 0.01p
Learn To Program In Excel Vba In Easy Steps, Colour (In Easy Steps Series) Paperback – 5 Feb 2004
There's a later one but I can't vouch for that, also:-
Excel VBA Programming for Dummies (For Dummies (Computers)) Paperback – 21 May 2010
Another one for 0.01p
There's also later one for that.