Posts by alansidman

    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

    crossposted: https://www.mrexcel.com/board/…sheet-name-daily.1151450/


    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

    Quicker and easier in PQ, but here is some code for you.


    I changed one line of your sample to IN TRANSIT. I then amended the code as follows. Added a new column that said, if Delivery Date is "IN TRANSIT" then change date to 1/1/1900 which allows you to then flag those deliveries.


    Using Power Query/Get and Transform, I have put the data into a normalized table. Here is the Mcode for that action. The file is attached for your review.


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"shipping out date", type date}, {"delivery date", type date}, {"Month", type text}, {"Ref", Int64.Type}, {"PO Number", type text}}),
    4. #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "PO Number", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"PO Number.1", "PO Number.2", "PO Number.3", "PO Number.4", "PO Number.5", "PO Number.6", "PO Number.7", "PO Number.8", "PO Number.9", "PO Number.10", "PO Number.11", "PO Number.12", "PO Number.13", "PO Number.14", "PO Number.15", "PO Number.16", "PO Number.17", "PO Number.18", "PO Number.19", "PO Number.20", "PO Number.21"}),
    5. #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"shipping out date", "delivery date", "Month", "Ref"}, "Attribute", "Value"),
    6. #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "PO"}}),
    7. #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
    8. in
    9. #"Removed Columns"

    I have amended your post for you to include Code Tags. These are required when you post code as outlined in the forum rules you agreed to when you joined. Please take a few minutes to re read the forum rules and abide by them in future postings.

    The following code will identify any number in Column A beginning with 2 and paste it into column F