Forum Discussion

4 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 22, 2023

    Vimal_Gaur 

     

    A 365 solution to consider:

     

    Dynamic range 'InputData' - this is all your data from column 3 to the end
=LET(n, Sheet1!$C$2:$M$10000, nonblank, COUNTA(TAKE(n, , 1)), TAKE(n, nonblank))

Dynamic range 'Names' - this is first two columns
=LET(n, Sheet1!$A$2:$B$10000, nonblank, COUNTA(TAKE(n, , 1)), TAKE(n, nonblank))

'Terms' - an array constant of terms for REDUCE to check
={"SGST"; "CGST"; "IGST"}

Lambda - 'Total' - this function filters each of the terms and stacks them horizontally.  An additonal stack is created for the totals columns.
=LAMBDA(a,v,LET(
    filtered, FILTER(InputData, TEXTBEFORE(TAKE(InputData, 1), " ") = v),
    HSTACK(
        a,
        filtered,
        BYROW(
            filtered,
            LAMBDA(row, IF(COUNT(row) = 0, TEXTBEFORE(TAKE(row, , 1), " ") & " Total", SUM(row)))
        )
    )
))

     

    Sheet level formula:

    =REDUCE(Names,Terms,Total)

     

     

    • Vimal_Gaur's avatar
      Vimal_Gaur
      Copper Contributor
      Feb 22, 2023

      NikolinoDE 

      Perhaps I’ve not able to explain the matter properly.

      I have several columns in a sheet

      I managed to deleted the columns other than SGST, CGST & IGST thru VBA.

      only columns remain are SGST, CGST & IGST having different tax categories (e.g. 5%, 18% etc)

      now need to total of SGST row-wise, CGST row-wise & IGST row-wise

      in separate column inserted automatically after the last column of each category.

      Plz see the sheets attached.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 22, 2023

        Vimal_Gaur 

        Here is some VBA code:

        Sub InsertTotals()
    Const HeaderRow = 2 ' row number of the headers
    Dim LastRow As Long
    Dim StartCol As Long
    Dim Col As Long
    Dim OldCat As String
    Dim NewCat As String
    Dim FillColor As Long
    LastRow = Cells(HeaderRow, 1).End(xlDown).Row
    Application.ScreenUpdating = False
    Col = 3
    StartCol = 3
    OldCat = Left(Cells(HeaderRow, Col).Value, 4)
    FillColor = Cells(HeaderRow, Col).Interior.Color
    Do
        Col = Col + 1
        NewCat = Left(Cells(HeaderRow, Col).Value, 4)
        If NewCat <> OldCat Then
            Range(Cells(HeaderRow, Col), Cells(LastRow, Col)).Insert Shift:=xlShiftToRight
            Cells(HeaderRow, Col).Value = "Total " & OldCat
            Range(Cells(HeaderRow + 1, Col), Cells(LastRow, Col)).FormulaR1C1 = _
                "=SUM(RC[" & StartCol - Col & "]:RC[-1])"
            With Range(Cells(HeaderRow, Col), Cells(LastRow, Col))
                .Borders.LineStyle = xlContinuous
                .Interior.Color = FillColor - RGB(20, 20, 20)
            End With
            Col = Col + 1
            StartCol = Col
            OldCat = NewCat
            FillColor = Cells(HeaderRow, Col).Interior.Color
        End If
    Loop Until NewCat = ""
    Application.ScreenUpdating = True
End Sub

Resources