Compile Data from Sheet 1 (Input Data) and Convert onto Sheet 2 (Output Data)

[Lee R]

Hi All,

Having a bit of trouble. Have attached Book 1.xlsx and information below of the outcome that I am looking for. Thanks in advance for all the assistance.

I have a table of sorts in sheet "Input Data" which has 3 columns. "#", "Seq" and "Name".

Column "#" will always have once blank cell between each row and will only ever range from 1 to 42, no number will ever be used twice or doubled up and will not necessarily be in order from 1 to 42.
Column "Seq" can be blank, or contain A to B, A to C, A to D, and so on up to A to Z and can be used multiple times. Example "Matt" and "Ash"
Column "Name" has the potential to be the same but not often.

In sheet "Output Data". Data is referenced from sheet "Input Data" this is to contain the below.

First row of each table as such is to contain information from "#" and "Name" from sheet "Input Data" and cells to be merged.
Second row of table is to contain "Seq" in first column, "Day" in second column and "Task" in third column.
Rows below that are to contain obviously the "Seq" from sheet "Input Data". Day and Task are to be blank at this stage and any information required will be input manually.
Information from sheet "Input Data" is to be input 2 rows below last found data above. The formatting/cell color of the cell is not required to be copied across; this is just to make it easy at this stage for your reference. If column "Seq" is blank in sheet "Input Data" then no data is required to be transferred across to sheet "Output Data" and to then move to next found "Seq" in input data. Example being "Lee" "Seq" is blank. this is to be skipped and go from "Peter" to Matt". However, if Lee does end up having A to B input into "Seq" column then a new table is to be inserted into "Output Data".

Ideally this is to be completed with VBA and is to run automatically all the time. So when data changes in sheet "Input Data" then sheet "Output Data" change accordingly.

Hope this helps.

Cheers,
Lee

[muhammadridho30]

disclaimer : as far as I know, because this is formula based and not vba based, the tables should be prepared first, so I have questions:
1. what is "maximum" data can be entered in this form?
2. what part in this table that inputted manually?

[jindon]

To Output Data sheet code module.

Private Sub Worksheet_Activate()
    Dim r As Range, LastR As Range, s As Long, i As Long, n As Long, f As Long, flg As Boolean
    Application.ScreenUpdating = False
    Columns("b:p").Clear
    For Each r In Sheets("input data").Columns("b").SpecialCells(2)
        If r.Interior.ColorIndex <> xlNone Then
            flg = Not flg
            Set LastR = Cells(Rows.Count, IIf(flg, "b", "j")).End(xlUp)(4)
            r.Resize(, 7).Copy LastR
            With LastR.Resize(, 7)
                s = Asc(Trim$(Split(.Cells(1, 2), "to")(0)))
                f = Asc(Trim$(Split(.Cells(1, 2), "to")(1)))
                .Cells(1).Value = Join(Array(.Cells(1), .Cells(1, 6)), " - ")
                .Rows(2).Resize(, 3) = [{"Seq","Day","Task"}]
                For i = 0 To f - s
                    .Cells(i * 2 + 3, 1) = Chr(s + i)
                Next
                With .Resize((i + 1) * 2 - 1)
                    .Borders.Weight = 2
                    .HorizontalAlignment = xlCenter
                    Application.DisplayAlerts = False
                    .Rows(1).Merge
                    For i = 2 To .Rows.Count
                        .Cells(i, 3).Resize(, .Columns.Count - 3).Merge
                    Next
                End With
            End With
        End If
    Next
    [b1:p2].Delete xlShiftUp: Rows.AutoFit
    Application.ScreenUpdating = True
End Sub