Forum Discussion

ajmal_pottekattil_yoousuf's avatar
ajmal_pottekattil_yoousuf
Iron Contributor
Apr 25, 2023
Solved

Transpose the value

Ledger Partner Debit Credit  Credit (VAT)   Turbine Services & Solutions Aerospace 3,570.00 3,400.00 170.00 Sales A/C         VAT SR Output           TRICON Trading & ...
excel
Excel for web
  • Lorenzo's avatar
    Lorenzo
    Apr 26, 2023

    ajmal_pottekattil_yoousuf 

    I don't see/understand the rounding issue you refer to. Instead of sharing video or Word doc. could you please:

    - Use your actual data

    - Refresh the query I shared

    - Highlight any rounding issue in the resulting table

    and then share the Excel file

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 25, 2023

ajmal_pottekattil_yoousuf 

With data formatted as Table named Table1

 

in G1:

=LET(
  k, SEQUENCE(ROWS(Table1)),
  l, MOD(k,3),
  e, IF(ISBLANK(Table1[[Ledger]:[Debit]]),"",Table1[[Ledger]:[Debit]]),
  s, INDEX(e,k,SEQUENCE(,3)),
  VSTACK(Table1[[#Headers],[Ledger]:[Credit]],
    HSTACK(s, IFS(l=1,"", l=2, INDEX(Table1[Credit],k-1), TRUE,INDEX(Table1[Credit (VAT)],k-2)))
  )
)
ajmal_pottekattil_yoousuf's avatar
ajmal_pottekattil_yoousuf
Iron Contributor
Apr 25, 2023
I am not using the updated Microsoft updated version.
I am using 2016 Version.

Resources