• Hi,


    I am just wondering if anyone in this forum could help me with my issue.

    On a weekly basis, I receive a report from a supplier ("Report" tab), where they state the delivery dates and various PO numbers in Column E.

    I then use delimited option to separate each PO individually ("Report (2)" tab).

    I have a different report from our internal system with the same PO numbers for which I need to find out the delivery date using the report received from supplier.

    Is it possible to lookup a value which potentially could be in any column between E & Y, but still return the result located in column Z.


    Until now I have been doing multiple VLOOKUP's, but it's rather time consuming so I was hoping that maybe there is an advanced VLOOKUP formula which can find out what I need in one go.

    Any other suggestions would also be much appreciated.


    Thanks,

    Jurate

  • 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"
  • Hi Alan,


    This is great! Thank you so much for the solution.


    The only thing I forgot to mention, sometimes there will be "IN TRANSIT" instead of the actual date in the delivery date column. I noticed that then the power query returns blank rows.


    Is there an easy fix for this? If not, not to worry I can still perfectly work with the file.


    Thanks,

    Jurate

  • 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.