Layout of dataset by week

[LeadingIndicator]

<a href=http://www.filedropper.com/lilayout><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >File Hosting Online Storage Backup</a></div>

http://www.filedropper.com/lilayout


Hello All,

Im seeking guidance - not on any super specific formulae or VBA code - but rather, I am at the very, very, very start of a tiny excel project and Im wanting to layout the dataset correctly.

Its nothing insanely large, but here it is:

1. there are about ~10 inspection forms.
2. each form has about ~10 questions - the answer is either 'Safe' or 'At-Risk'.
3. I will collate all of the forms on a weekly basis - for one year.

The question is:

What is the best way to tabulate/set out the data in excel.

I have used a file dropped to upload the excel file - and that will show you the ways I have come up with.

But, I havent been able to see a way which will work without merging (which is evil on pivot tables, formulae etc).

Any advice, input, anything is all welcome.

thank you.

 

[DILIPandey]

Hi LeadingIndicator,

I checked your file and below are my thoughts :-

1) is this excel is online speadsheet questionnaire ?
2) If merged cells are your problem, you can use below macro to unmerge them:-

Sub UnMergeFill()Dim cell As Range, joinedCells As Range
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next
End Sub

3) For weekly compilation, you can insert weekly columns in between the data or at the end (right side) and then you can use sum function powered with offset to get the weekly range summed up.


Let me know your thoughts.

Regards,
DILIpandey