• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate Cascaded Data - Various Headers

yorkbay

New Member
Hi - Newbie here...I have 11 Excel Tabs
1st Sheet is named 'Index' where a user defines a set of Names for policies, i.e. Salary Full Time, Hourly Part time, Seasonal etc.

The remaining 10 sheets (we refer to them as chapters) which contain their own set of questions based on requirements
Sheet 2 is named Punch & Schedule and contains 121 questions related to punch and schedules
Sheet 3 is named Time of Day Premiums and contains 60 questions related to Time of Day Premiums
Sheet 4 is named Day of Week Premiums and contains 72 questions related to Time of Day Premiums
Sheet 5 is named Overtime and contains 49 questions related to Overtime policies

Once the user creates their names on the Index VBA macros allow them to choose a name and a chapter they want to fill out
Doing so pulls the Policy Name into the Chapter
Chapters have questions in column D, examples of input in column E
The policy name they choose to input appears in column F (copied from Index when they select it)
Column F has VBA code that shows/hides questions based on response so some of the questions etc.
When they are done answering the set of questions they press a save macro which moves the saved name policy to column G, H, I etc.

Not all Policy Names will appear on all chapters i.e. Full Time Salary may not ever have any Overtime information
The Policy Names moved to columns G, H, I on all tabs can be in any order because different users come in and fill out whichever they want based on their knowledge

I've now been requested to provide a Master sheet with all questions and all responses from each Sheet in live time so that reviewers can check progress on the teams filling out the information

I have set up a master sheet with the questions 1-536 in column C (A and B are hidden for admin purposes).
I've used the Index function to pull in up to 40 policy names from each tab because I'll never know how many there are - could be 1 up to 40. (I'd like to only pull used columns from each tab but couldn't figure out how to do this)
These 40 headers from each Chapter Sheet are 'index referenced' in header row 1 of the Master Sheet
I've cascaded diagonally the Index responses to each sheet questions
My thought was to then consolidate all records with same headers and then hide the blank columns to ultimately have all policy names with their responses in a vertical format but I'm stuck.

In the attached the green highlight means it's a new Chapter. You can see some chapters have no information yet
Other chapters have 1 or 2 policy names populated with information
I've hidden the unused columns so you can see it better to understand what I'm trying to do as questions and responses need to align for each policy. any assistance would be much appreciated.
 

Attachments

Last edited:

Marc L

Excel Ninja
Hi, according exclusively to your attachment a VBA demonstration as a starter :​
Code:
Sub Demo1()
        Dim C%, F%, rA As Areas, L&
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Columns
        For C = .Count To .Cells(2, 1).End(xlToRight).Column + 1 Step -1
               F = Application.Match(.Cells(C).Value2, .Rows(1), 0)
            If F < C Then
                Set rA = .Item(C).SpecialCells(xlCellTypeConstants).Areas
                For L = 2 To rA.Count:  rA(L).Copy .Cells(rA(L).Row, F):  Next
                .Item(C).Delete
            End If
        Next
    End With
        Application.ScreenUpdating = True
        Set rA = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

yorkbay

New Member
This works beautifully in my initial testing .Will run a full test to ensure full circle working but I cannot thank you enough and very much appreciate the assistance! Would you mind providing line by line explanation on how the code works as I'm trying to learn and understand. Thank you again!
 

Marc L

Excel Ninja
First see in VBA help as all is there ! And in Excel help for MATCH worksheet function if necessary …​
 
Top