Arranging/organizing data in a sheet

HI everybody,

I've been burning my head trying to find a solution for my problem, and not achieving to do so, I came here for your precious help!

I have one sheet in one workbook that contains the data I want to organize. In this data, I have several datasets placed horizontally, in the side of each other.

Each "dataset" correspond to a month containing daily values of several headers. So, it is organized as:

Date Header_1 Header_2 ...
01/01/1990 value value
... ... ...
31/01/1990 value value

The number of headers in each "dataset" vary, but they can or not repeat themselves in the others "datasets".

Please see the uploaded file for better understanding.

What I want to do is to organize my data chronologically into a single one database containing all information. Two things have to be done here:
1 - Some function/macro to transfer the horizontally arranged "datasets" into a one new "database" organized chronologically
2 - Some function/macro to match the headers. Ex: Dataset_2 is to be placed below dataset_1. If header_1 of "dataset_2" already exists in dataset_1, I want the values of header_1 of dataset_2 to "merge" with already existant header_1 of dataset_1.

I hope I was clear enough.

Thank you very much!

Are all possible column headers known? Or could you receive new ones at some future date?

Yes, I know all possible headers (155 different headers).

In my example I put only 3 "datasets", but I have 300+ datasets tp organize in the same way

Thank you!

Sub dataset()
cl = 2
cmax = 2

While Sheets("sheet8").Cells(3, cl) <> ""
With Worksheets("hoja1").Range("a3:zz3")
    Set c = .Find(Sheets("sheet8").Cells(3, cl), LookIn:=xlValues)
    If c Is Nothing Then
        Sheets("hoja1").Cells(3, cmax) = Sheets("sheet8").Cells(3, cl)
        cmax = cmax + 1
    End If
End With
cl = cl + 1
Wend

cright = Sheets("sheet8").Cells(3, cl - 1).Address
cright = Left(cright, Len(cright) - 1)

r = 4
r2 = 4
While Sheets("sheet8").Cells(r, 2) <> ""
cl = 2
While Sheets("sheet8").Cells(3, cl) <> ""
If Sheets("sheet8").Cells(3, cl) = "DATE" Then
r2 = r2 + 1
Sheets("hoja1").Cells(r2, 2) = Sheets("sheet8").Cells(r, cl)
Else
With Worksheets("hoja1").Range("a3:" & cright & "3")
    Set c = .Find(Sheets("sheet8").Cells(3, cl), LookIn:=xlValues)
    If Not c Is Nothing Then
        Sheets("hoja1").Cells(r2, c.Column) = Sheets("sheet8").Cells(r, cl)
    End If
End With
End If
cl = cl + 1
Wend
r = r + 1
Wend
   
    With ActiveWorkbook.Worksheets("Hoja1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B4:B" & r2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("B3:" & cright & r2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

How do I declare all variables in this code?

Thanks

The code will look for all possible headers first come first posted
The number of columns analyzed is determined by the headings
The "date" column is automatically inserted in a new row
the sort routine adjust its range according to the rows and columns in the sheet

in post #5 do you mean how is the output sheet cleared?

Originally Posted by rcm

The code will look for all possible headers first come first posted
The number of columns analyzed is determined by the headings
The "date" column is automatically inserted in a new row
the sort routine adjust its range according to the rows and columns in the sheet

in post #5 do you mean how is the output sheet cleared?

I think I forgot to paste your macro outside the "option explicit" default in the VBA editor. Once I deleted that "option explicit" your macro worked like a charm!

Sorry for my ignorance.

I can't thank you enough!!!

you are welcome just mark it as solved...