Transpose the value

[ajmal pottekattil yoousuf]

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 & Contracting CO.	525.00	500.00	25.00
Sales A/C
VAT SR Output
	WESCO L.L.C	5,124.00	6,100.00	244.00
Sales A/C
VAT SR Output
	NTS Middle East FZCO	2,756.25	2,625.00	131.25
Sales A/C
VAT SR Output

Credit and Credit (Vat) Value should be change to column vise.

please refer the video.

[#2]

@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)))
  )
)

[#3]

I am not using the updated Microsoft updated version.
I am using 2016 Version.

[#4]

You tagged your initial post with 'Excel for web' that has all the functions used in the suggested solution

[#5]

Sorry for this mistake.
Could you please guide me .to how to solve this issue.

[#6]

@ajmal pottekattil yoousuf 

See sheet 'XL 2016' in attached workbook

[#7]

@ajmal pottekattil yoousuf 

Something less painful than formulas with Excel 2016, Get & Transform aka Power Query

In attached file:

#1 Put your data in 'Table1'

#2 Switch to tab 'Power Query'

#3 Right-click in the green table > Refresh

[#8]

@L z. 

Could you please guide me .

I am first time use the power query.

[#9]

@ajmal pottekattil yoousuf 

What exactly is your question?

[#10]

https://v.douyin.com/DPgVvWg

how about online tool.

[#11]

The above link the how to transpose the value is not showing .
The length of the video is 40 sec only.
After the going to the power query what tool I am used to get the desired output.
Could you please guide me .

[#12]

The above link the how to transpose the value is not showing .

？？
What exactly is your question?

http://e.anyoupin.cn/EData/?s=Transpose

Above is the online tool.
If possible,you can try it.

[#13]

@ajmal pottekattil yoousuf 

I am first time use the power query. Just a few links:

• Getting started with Get & Transform
• Guide to the Power Query ribbon
• View and Manage Queries in a Workbook
• Power Query tutorials
• Power Query 101
• Modify a query formula
• Add, Edit, Delete…query step
• Power Query M formula language ref.
• Power Query Complete M Code Intro.

[#14]

My question is that Could you please give me the step you achieve to solve the issue.
you given the power query table is working perfectly.
Could you please give me the "How you create that output"
Please guide me .

[#15]

@L z. 

one of the issue is faced is that rounding error of the value.

[#16 · Accepted solution]

@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

[#17]

@L z. 

please refer the highlighted cell.

Malik Traders 

[#18]

@ajmal pottekattil yoousuf 

In your initial post you had 5 columns: [Ledger]...[Credit (VAT)], in the workbook you just shared there's an extra [Column1]:

Not really a suprise you have issue(s)

I give up

If someone else wants to take it over feel free

[#19]

This is downloaded from the software
The people providing different excel files
Please help me
I will give the correct file
Please help

[#20]

@ajmal pottekattil yoousuf 

Credit (Discounted Sales)	Credit (VAT)
2,800.00	140.00

The required output is 

We have to transpose this value.

please guide me to solve this