Try PowerQuery.
Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade...
www.microsoft.com
M Code (Source):
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Destination", type text}, {"Japan", type any}, {"USA", type any}, {"Swiss", type any}, {"Singapore", type any}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Japan] <> "Qty")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"No Doc", type text}, {"IND/07/01/1", Int64.Type}, {"IND/07/01/2", Int64.Type}, {"IND/07/01/3", Int64.Type}, {"IND/07/01/4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1", "No Doc"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, Lookup, {"Value"}, "Lookup", JoinKind.LeftOuter),
#"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Attribute"}, {"Attribute.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Lookup",{{"Value", "Qty"}, {"No Doc", "Description"}, {"Attribute", "No Doc"}, {"Column1", "Code"}, {"Attribute.1", "Destination"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Destination] = "Japan" or [Destination] = "USA")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Destination", Order.Ascending}, {"Description", Order.Ascending}})
in
#"Sorted Rows"
M Code (Lookup):
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Destination", type text}, {"Japan", type any}, {"USA", type any}, {"Swiss", type any}, {"Singapore", type any}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Japan] <> "Qty")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
#"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Code", "Destination"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value")
in
#"Unpivoted Columns"