Transpose column header and insert same row for the column header

A

askall1000

Board Regular
Joined
Jan 3, 2019
Messages
58
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

I want to convert column headers and then insert rows for usd, eur and other currencies, when transpose this data i will calculate x currency with f/x rate. I want to transpose with formula because i need to do this transpose each month. Thank you very much for your help in advance (I am using company laptop, sorry for lack of add-ins that i can upload, i could only share image of excel, my Ms Excel version is 365).

1695363948518.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Here's my stab at it with Power query

Book1
ABCDEFGHI
1A/CUSDX CurrencyEURX Currency2GBPX Currency3CHFX Currency4
2ComA100015009001600
3ComB10001100
4ComC1200170050002500
5
6
7A/CAttributeValueTable1 (2).Value
8ComAUSD10001500
9ComAEUR9001600
10ComBGBP10001100
11ComCUSD12001700
12ComCCHF50002500
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A/C", type text}, {"USD", Int64.Type}, {"X Currency", Int64.Type}, {"EUR", Int64.Type}, {"X Currency2", Int64.Type}, {"GBP", Int64.Type}, {"X Currency3", Int64.Type}, {"CHF", Int64.Type}, {"X Currency4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A/C"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table1 (2)", {"Index.1"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Value", "Index.1"}, {"Table1 (2).Value", "Table1 (2).Index.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each ([Attribute] = "CHF" or [Attribute] = "EUR" or [Attribute] = "GBP" or [Attribute] = "USD")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Table1 (2).Index.1"})
in
    #"Removed Columns"
 
Upvote 0
Kerryx said:
Here's my stab at it with Power query

Book1
ABCDEFGHI
1A/CUSDX CurrencyEURX Currency2GBPX Currency3CHFX Currency4
2ComA100015009001600
3ComB10001100
4ComC1200170050002500
5
6
7A/CAttributeValueTable1 (2).Value
8ComAUSD10001500
9ComAEUR9001600
10ComBGBP10001100
11ComCUSD12001700
12ComCCHF50002500
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A/C", type text}, {"USD", Int64.Type}, {"X Currency", Int64.Type}, {"EUR", Int64.Type}, {"X Currency2", Int64.Type}, {"GBP", Int64.Type}, {"X Currency3", Int64.Type}, {"CHF", Int64.Type}, {"X Currency4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A/C"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table1 (2)", {"Index.1"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Value", "Index.1"}, {"Table1 (2).Value", "Table1 (2).Index.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each ([Attribute] = "CHF" or [Attribute] = "EUR" or [Attribute] = "GBP" or [Attribute] = "USD")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Table1 (2).Index.1"})
in
    #"Removed Columns"
Click to expand...
Thank you very much for your help and valuable time, sorry my ignorance with power query. I paste your script to advanced editor and it gave error and i don't want to waste your valeable time and keep you busy for every error. I appreciate if you could help to me with formula, if you are available. Again appreciated your help with power query.
 
Upvote 0
askall1000 said:
Hello All,

I want to convert column headers and then insert rows for usd, eur and other currencies, when transpose this data i will calculate x currency with f/x rate. I want to transpose with formula because i need to do this transpose each month. Thank you very much for your help in advance (I am using company laptop, sorry for lack of add-ins that i can upload, i could only share image of excel, my Ms Excel version is 365).

View attachment 99127
Click to expand...
Another idea using a formula.

It involves some Helper columns in red. If need be these can be on the right. The formula would need to change though.

It assumes that the data is in a sheet named 'Data'.

Transpose column header and insert same row for the column header.xlsm
ABCDEFGHIJ
1Account NameUSDUSDX CurrencyEUREURX CurrencyGPDGPDX Currency
2Company AUSD10001500EUR9001600GPD
3Company BUSD344545EURGPD6566656
4Company CUSDEUR6657887GPD8788878
5Company DUSD2133656EUR45455656GPD6656665
Data


Transpose column header and insert same row for the column header.xlsm
ABCD
1Account NameCurrencyCurrency AmountX Currency
2Company AUSD10001500
3Company AEUR9001600
4Company BUSD344545
5Company BGPD6566656
6Company CEUR6657887
7Company CGPD8788878
8Company DUSD2133656
9Company DEUR45455656
10Company DGPD6656665
Results
Cell Formulas
RangeFormula
A2:D10A2=SORT(VSTACK(FILTER(CHOOSECOLS(Data!$A$2:$J$5,{1,2,3,4}), Data!$C$2:$C$5>0),FILTER(CHOOSECOLS(Data!$A$2:$J$5,{1,5,6,7}), Data!$F$2:$F$5>0),FILTER(CHOOSECOLS(Data!$A$2:$J$5,{1,8,9,10}), Data!$I$2:$I$5>0)))
Dynamic array formulas.
 
Upvote 0
Solution
Here is my effort, when I started I though "let's keep this formula short"...
I am sure someone will come along with a shorter version.

Book1
ABCDEFGHI
1A/CUSDX CurrencyEURX Currency2GBPX Currency3CHFX Currency4
2Company A100015009001600
3Company B2000110010001100100200
4Company C500100500500
5
6
7Account NameCurrencyCurrency AmountX Currency
8Company AUSD10001500
9Company AEUR9001600
10Company BUSD20001100
11Company BGBP10001100
12Company BCHF100200
13Company CEUR500100
14Company CCHF500500
Sheet1
Cell Formulas
RangeFormula
A8:D14A8=LET( r,Table1[[#Headers],[USD]:[X Currency4]]&"|"&Table1[[USD]:[X Currency4]], t,WRAPROWS(TOROW(r),2), a,TOCOL(IF(SEQUENCE(,COLUMNS(r)/2)<=COLUMNS(r)/2,Table1[A/C],#N/A),3), c,TEXTBEFORE(INDEX(t,,1),"|"), ca,IFERROR(--TEXTAFTER(INDEX(t,,1),"|"),""), x,IFERROR(--TEXTAFTER(INDEX(t,,2),"|"),""), f,HSTACK(a,c,ca,x), FILTER(f,INDEX(f,,3)<>"") )
Dynamic array formulas.
 
Upvote 0
askall1000 said:
my Ms Excel version is 365
Click to expand...
In that case you might want to update your profile.
Another option
Fluff.xlsm
ABCDEFGHI
1A/CUSDX CurrencyEURX Currency2GBPX Currency3CHFX Currency4
2Company A100015009001600
3Company B2000110010001100100200
4Company C500100500500
5
6
7Account NameCurrencyCurrency AmountX Currency
8Company AUSD10001500
9Company AEUR9001600
10Company BUSD20001100
11Company BGBP10001100
12Company BCHF100200
13Company CEUR500100
14Company CCHF500500
Data
Cell Formulas
RangeFormula
A8:D14A8=HSTACK(TOCOL(IF(CHOOSECOLS(B2:H4,1,3,5,7)<>"",A2:A4,1/0),2),TOCOL(IF(CHOOSECOLS(B2:H4,1,3,5,7)<>"",CHOOSECOLS(B1:H1,1,3,5,7),1/0),2),WRAPROWS(TOCOL(B2:I4,1),2))
Dynamic array formulas.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Kerryx said:
Here's my stab at it with Power query

Book1
ABCDEFGHI
1A/CUSDX CurrencyEURX Currency2GBPX Currency3CHFX Currency4
2ComA100015009001600
3ComB10001100
4ComC1200170050002500
5
6
7A/CAttributeValueTable1 (2).Value
8ComAUSD10001500
9ComAEUR9001600
10ComBGBP10001100
11ComCUSD12001700
12ComCCHF50002500
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A/C", type text}, {"USD", Int64.Type}, {"X Currency", Int64.Type}, {"EUR", Int64.Type}, {"X Currency2", Int64.Type}, {"GBP", Int64.Type}, {"X Currency3", Int64.Type}, {"CHF", Int64.Type}, {"X Currency4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A/C"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table1 (2)", {"Index.1"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Value", "Index.1"}, {"Table1 (2).Value", "Table1 (2).Index.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each ([Attribute] = "CHF" or [Attribute] = "EUR" or [Attribute] = "GBP" or [Attribute] = "USD")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Table1 (2).Index.1"})
in
    #"Removed Columns"
Click to expand...
One addition while i was updating my profile to 365, i saw i thicked power query, really sorry for misleading.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Pull exchange data from another workbook
Replies
2
Views
91
Maniac189
M
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
R
  • Question Question
VBA Inserting rows based on Column criteria
Replies
4
Views
106
myall_blues
myall_blues
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
127
sijpie
sijpie
D
  • Question Question
Create empty table (with headings) in Word document after some text
Replies
5
Views
519
dwsteyl
D

Forum statistics

Threads
1,224,813
Messages
6,181,114
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top