• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

paste all columns across to 1 coloumn

dgk

Member
Hi
need a macro to copy qty information from all columns, to a different worksheet to 1 column
original worksheet each column represents a week range {which is the header]
basically
copy
item no to column A
description to column B
qty to column C [most important]
date to column D
change column D to a meaningful DATE value either by using the beginning date, or the end date
see sample attached
Need to transfer all data from the SALES sheet to DESIRED sheet

[original file has more than 80 columns & about 400 items.....]

Thanks
 

Attachments

  • WEEKLY_SALES-SHEET.xlsm
    16.9 KB · Views: 4
Using Power Query, its a case of unpivot data and sorting, etc.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([ITEMNO] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"ITEMNO", "Description"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Date"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Sept","Sep",Replacer.ReplaceText,{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"
 

Attachments

  • WEEKLY_SALES-SHEET.xlsm
    30.4 KB · Views: 5
Back
Top