Merge partial character before specific word across sheets based on headers

Hi,
I want merge data based on matching headers across sheets .
if the cells B4,C4 are empty then should match header(DETAILES) and merge amounts for column TOTAL based on partial character before word "NO"
if the cells B4,C4 are DATES then should match headers(DATE,DETAILES) and merge amounts for column TOTAL based on before word "NO"
as you see the structure for each sheet is not the same . so that's why I would match headers.
so the result will be in RESULT sheet based on what I put it if dates in DATES sheet and if there is no date in WITHOUT DATES sheet.
in row 7 will be headers from column C based on sheets names and will merge based on two cases as I mentioned also insert total row to sum each column .
I would macro to deal with big data for each sheet , I will add new sheets every time before RESULT sheet . so before bring report should delete data in RESULT sheet.
also posted here
https://www.mrexcel.com/board/thread.../#post-6204828

A formula solution .....

in H8 of DATES

Formula:

=SUMIFS(PURCHASES!$F$2:$F$10000,PURCHASES!$C$2:$C$10000,">=" & IF($B$4="",DATEVALUE("01/01/2000"),$B$4),PURCHASES!$C$2:$C$10000,"<=" & IF($C$4="",DATEVALUE("31/12/2099"),$C$4))

See attached: sample results in G:J of "DATES"

INDIRECT could be used to match headers in C7:F7 but if these are constant then using the fixed sheet names will suffice.

Numbers will automatically refresh as input sheets are updated but if this is a problem then VBA is the option (as you requested!)

NOTE: some dates in "SALES" are mis-typed!

@Amer omar

This is not the first time that you have addressed someone trying to help you in a disparaging tone. If you wish to continue being allowed to get help here, this must STOP.

Thank you.

The "Dates" sheet was purely used as an example (for testing) but I will now leave it to others as you evidently find it too onerous to transfer the formulae to RESULT sheet!

The tone of this:

I'm not sure if you read well my post !
should create the whole report in RESULT sheet .
did you see the RESULT sheet ?

and of some comments in earlier threads by you is not very polite. You seem to be accusing your helper of incompetence. It may be that your first language is not English, so you are unaware of how this comes across. You can look up the word 'dosparaging' online.

Thank you for modifying your tone to make it less critical. Thank you.

See attached: and yes, you need VBA!

Is there any reason why ALL your input sheets do not start in column A instead of a "random" set of selction A,B or C?

Option Explicit
Sub demo_vba()

Dim a, Hdrs, totals
Dim s As Long, i As Long, j As Long, n As Long, lc As Long, lr As Long, ws_count As Long
Dim cd As Long, ct As Long, ns As Long, fd As Long, td As Long
Dim ws As Worksheet
Dim key As String

Dim dict As Object

Application.ScreenUpdating = False

Set dict = CreateObject("Scripting.Dictionary")

If Range("fDate") = "" Then fd = DateValue("01/01/2020") Else fd = Range("fDate") ' Set Default date if no date entered
If Range("tDate") = "" Then td = DateValue("31/12/2099") Else td = Range("tDate") ' Set Default date if no date entered


ws_count = ActiveWorkbook.Worksheets.Count                                        ' Count of sheets

ReDim b(1 To 100, 1 To ws_count + 1)
ReDim Hdrs(1 To 1, 1 To ws_count + 1)
ReDim Total(1 To 1, 1 To ws_count - 1)

Hdrs(1, 1) = "ITEM": Hdrs(1, 2) = "DESCRIBE": ns = 0                              ' Headings

With dict
             
    For s = 1 To ws_count
        If Worksheets(s).Name <> "RESULT" Then
            Set ws = Worksheets(s)
            ns = ns + 1
            Hdrs(1, ns + 2) = Worksheets(s).Name                                  ' Add sheet name ("TX Type") to headings
            
            With ws
            
              lc = .Cells(1, Columns.Count).End(xlToLeft).Column                  ' Last column
              lr = .Cells(Rows.Count, lc).End(xlUp).Row                           ' Last row
              
              a = .UsedRange                                                      ' Input sheet data
              
              For j = 1 To UBound(a, 2)
                If a(1, j) = "DETAILS" Then                                       ' "DETAILS" column number
                    cd = j
                Else
                  If a(1, j) = "TOTAL" Then ct = j                                ' "TOTAL" column number
                End If
              Next j
                            
              For i = 2 To UBound(a, 1)
               If a(i, cd) <> "" Then
                    key = Mid(a(i, cd), 1, InStr(1, a(i, cd), "NO") - 2)          ' Set "KEY" (text prior to "NO" in "DETAILS" column)
                    If Not dict.exists(key) Then
                        n = n + 1
                        dict.Add key, n
                        b(n, 1) = n: b(n, 2) = key                                ' Add ITEM number plus KEY to output array
                    End If
                    If a(i, cd - 1) >= fd And a(i, cd - 1) <= td Then             ' Check if in date range ....
                       b(dict.Item(key), s + 2) = b(dict.Item(key), s + 2) + a(i, ct) ' Accumulate total for this "sub-TX Type""
                       Total(1, s) = Total(1, s) + a(i, ct)                       ' "TX Type" total ("PURCHASE","SALES" etc)
                    End If
                End If
              Next i
              
            End With
        End If
    
    Next s
    
    
End With

With Sheets("RESULT")
    .[A7].Resize(100, 50).Clear
    .[A7].Resize(1, ws_count + 1) = Hdrs: .[A7].Resize(1, ws_count + 1).Font.Bold = True
    .[A7].Resize(1, ws_count + 1).HorizontalAlignment = xlCenter
    .[A8].Resize(n, ws_count + 1) = b
    .Cells(n + 8, "A") = "TOTAL"
    .Cells(n + 8, "C").Resize(1, ws_count - 1) = Total
    .Cells(n + 8, "A").Resize(1, ws_count + 1).Font.Bold = True
    .[A7].Resize(n + 2, ws_count + 1).Borders.Weight = 2
    .[C8].Resize(n + 1, ws_count - 1).NumberFormat = "#,0.00"
End With

Application.ScreenUpdating = True
End Sub

.[C8].Resize(n + 1, ws_count - 1).NumberFormat = "#,0.00;-#,0.00;-"

Missing full stop at beginning

Need to set blank cells in array to zero

Option Explicit
Sub demo_vba()

Dim a, Hdrs, totals
Dim s As Long, i As Long, j As Long, n As Long, lc As Long, lr As Long, ws_count As Long
Dim cd As Long, ct As Long, ns As Long, fd As Long, td As Long
Dim ws As Worksheet
Dim key As String


Dim dict As Object

Application.ScreenUpdating = False

Set dict = CreateObject("Scripting.Dictionary")

If Range("fDate") = "" Then fd = DateValue("01/01/2020") Else fd = Range("fDate") ' Set Default date if no date entered
If Range("tDate") = "" Then td = DateValue("31/12/2099") Else td = Range("tDate") ' Set Default date if no date entered


ws_count = ActiveWorkbook.Worksheets.Count                                        ' Count of sheets

ReDim b(1 To 100, 1 To ws_count + 1)
ReDim Hdrs(1 To 1, 1 To ws_count + 1)
ReDim Total(1 To 1, 1 To ws_count - 1)

Hdrs(1, 1) = "ITEM": Hdrs(1, 2) = "DESCRIBE": ns = 0                              ' Headings

With dict
             
    For s = 1 To ws_count
        If Worksheets(s).Name <> "RESULT" Then
            Set ws = Worksheets(s)
            ns = ns + 1
            Hdrs(1, ns + 2) = Worksheets(s).Name                                  ' Add sheet name ("TX Type") to headings
            
            With ws
            
              lc = .Cells(1, Columns.Count).End(xlToLeft).Column                  ' Last column
              lr = .Cells(Rows.Count, lc).End(xlUp).Row                           ' Last row
              
              a = .UsedRange                                                      ' Input sheet data
              
              For j = 1 To UBound(a, 2)
                If a(1, j) = "DETAILS" Then                                       ' "DETAILS" column number
                    cd = j
                Else
                  If a(1, j) = "TOTAL" Then ct = j                                ' "TOTAL" column number
                End If
              Next j
                            
              For i = 2 To UBound(a, 1)
               If a(i, cd) <> "" Then
                    key = Mid(a(i, cd), 1, InStr(1, a(i, cd), "NO") - 2)          ' Set "KEY" (text prior to "NO" in "DETAILS" column)
                    If Not dict.exists(key) Then
                        n = n + 1
                        dict.Add key, n
                        b(n, 1) = n: b(n, 2) = key                                ' Add ITEM number plus KEY to output array
                    End If
                    If a(i, cd - 1) >= fd And a(i, cd - 1) <= td Then             ' Check if in date range ....
                       b(dict.Item(key), s + 2) = b(dict.Item(key), s + 2) + a(i, ct) ' Accumulate total for this "sub-TX Type""
                       Total(1, s) = Total(1, s) + a(i, ct)                       ' "TX Type" total ("PURCHASE","SALES" etc)
                    End If
                End If
              Next i
              
            End With
        End If
    
    Next s
    
    
End With

For i = 1 To n
    For j = 3 To ws_count + 1
    If b(i, j) = "" Then b(i, j) = 0
    Next j
Next i


With Sheets("RESULT")
    .UsedRange.Offset(6).Clear
    .[A7].Resize(1, ws_count + 1) = Hdrs: .[A7].Resize(1, ws_count + 1).Font.Bold = True
    .[A7].Resize(1, ws_count + 1).HorizontalAlignment = xlCenter
    .[A8].Resize(n, ws_count + 1) = b
    .Cells(n + 8, "A") = "TOTAL"
    .Cells(n + 8, "C").Resize(1, ws_count - 1) = Total
    .Cells(n + 8, "A").Resize(1, ws_count + 1).Font.Bold = True
    .[A7].Resize(n + 2, ws_count + 1).Borders.Weight = 2
    .[C8].Resize(n + 1, ws_count - 1).NumberFormat = "#,0.00;-#,0.00;-"
End With

Application.ScreenUpdating = True
End Sub

Sorry - updated file