Posts by AlanSidman

    An alternative solution is to use Power Query. Load your range to PQ. Highlight all columns and click on Unpivot. The Mcode follows


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    4. #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    5. #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
    6. in
    7. #"Removed Columns"

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the # icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

    Not sure which dates need to be converted. Suggest you provide more detailed information about your worksheet and what you want to happen. Your explanation and worksheet are not clear. We are not mind readers here, but we are excellent excel helpers.

    Tried a different method


    Left out step using Locale. See if that works for you.

    Code Tags Added

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the "</>" icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

    crossposted: https://www.excelforum.com/off…hs-and-12-months-ago.html

    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.


    Post a link to any other forums where you have asked the same question.


    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.


    Go to https://www.excelguru.ca/content.php?184 to understand why we ask you to do this.


    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

    Code Tags Added

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the # icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

    Using Power Query/Get and Transform, here is the Mcode for solving your issue


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    4. #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    5. #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type with Locale", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    6. #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    7. #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date.2", "Date.1", "Date.3"}),
    8. #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.2", type text}, {"Date.1", type text}, {"Date.3", type text}}, "en-US"),{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged")
    9. in
    10. #"Merged Columns"

    Files

    • Book3.xlsx

      (18.46 kB, downloaded 6 times, last: )

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.


    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.


    2. Make sure that your desired results are also shown (mock up the results manually).


    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).


    4. Try to avoid using merged cells as they cause lots of problems.


    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

    Here is the new Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", Int64.Type}, {"Column21", type number}, {"Column22", Int64.Type}, {"Column23", type number}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", type number}, {"Column28", type number}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", type number}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", type number}, {"Column38", type number}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", type number}, {"Column42", Int64.Type}, {"Column43", type number}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", Int64.Type}, {"Column51", type number}, {"Column52", Int64.Type}, {"Column53", type number}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", Int64.Type}, {"Column61", type number}}),
    4. #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "subtotal")),
    5. #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"),
    6. #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
    7. #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"})
    8. in
    9. #"Removed Columns"
    Code
    1. If you have multiple sheets that need to be merged into the solution, then you can add each to PQ, Append each to the other and then run your query (Mcode). This is a quick and easy reformatting that you are requesting. Do a bit of research on Power Query and you may be surprised at what you can accomplish.

    Here is a VBA solution. I do not have a Formula solution for you.