Copy data from one tab to another.

[philb99]

Hi

I have MI recorded on one tab going left to right and I wish to show this in table formats going down on tab 2

Looking for help on how best to achieve please

Thanks

Dropbox

www.dropbox.com

 

[alansidman]

The easiest way is to copy the range of data and then Paste Transpose. You can use the following short cut keys
To Copy CTL +C
To Paste Transpose Alt → H → V → T

 

[philb99]

The easiest way is to copy the range of data and then Paste Transpose. You can use the following short cut keys
To Copy CTL +C
To Paste Transpose Alt → H → V → T

Many thanks be coming back so quickly.

Unfortunately that will not work given the black columns (Splitting Quarters) in the file and I am looking for an automated solution

 

[alansidman]

Perhaps then Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"12/1/2023 12:00:00 AM", "Dec 23"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

[philb99]

Perhaps then Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"12/1/2023 12:00:00 AM", "Dec 23"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Thank you for helping but Power Queries are way over my head

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[philb99]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Many thanks , so based on my Drop Box file how would the code now look please.

I have worked through the video but it is advising that it can not fine Table 1

Any help greatly appreciated

 

[philb99]

Perhaps then Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"12/1/2023 12:00:00 AM", "Dec 23"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Many thanks , so based on my Drop Box file how would the code now look please.

I have worked through the video on how to use Power Query but it is advising that it can not fine Table 1

Any help greatly appreciated

 

[kvsrinivasamurthy]

Try. In A3 of sheet2

=LET(a,TRANSPOSE(Sheet1!B1:Q2),FILTER(a,INDEX(a,,1)<>""))

 

[philb99]

Try. In A3 of sheet2

=LET(a,TRANSPOSE(Sheet1!B1:Q2),FILTER(a,INDEX(a,,1)<>""))

Thank you for trying to help - if you mean B3 in Sheet 2, then the prompt states that Function isn't valid - I'm in 2013