Advanced Excel Table Sorting

I need to sort my spreadsheet as follows:

The overall sorting criteria is based on the WAREHOUSE, BAY LOCATION and SHELF IF the product has been designated "YES" in the PICKING Column. You will notice the text in red is sorted alphabetically in ascending order by WAREHOUSE then by BAY LOCATION.

HOWEVER I need to find all other warehouse locations that match the PRODUCT CODE and display that data underneath that same PRODUCT CODE but this data must be sorted by BBD and then display the WAREHOUSE, BAY LOCATION and SHELF.

This spreadsheet is to help with Best Before Date rotation in a Warehouse that stores Candy. It will allow people to walk around the warehouse bay by bay and have a printed out sheet that displays the picking bay locations sorted in the location they appear in that warehouse while also displaying all the other locations that same product is stored in various other warehouses ordered by BBD.

I do not know what road to go down to achieve this solution. VBA? Pivot Table? or can it be achieved through functions?

If I understood it correctly you mean to first sort the products that are set to picking = "YES" based on the Warehouse / Bay Location / Shelf in ascending order, but keep the picking = "No" lines underneath the same product code lines and have them sorted on the BBD / Warehouse / Bay Location / Shelf.

We could do this in Microsoft 365 using the following formula:

=LET(d, A2:F10,
     o, HSTACK(d,XMATCH(INDEX(d,,1),
                        TAKE(SORT(FILTER(CHOOSECOLS(d,3,4,5,1),INDEX(d,,6)="YES"),{1,2,3,4}),,-1))),
DROP( 
     SORT(o,
           {7,6,2,3,4,5},
           {1,-1,1,1,1,1}),
     ,-1))

Explanation:
I changed you sample data to be in random order and changed one of the warehouses for the picking Yes type, to demonstrate the filter order better:

unsorted data:

In this example the following lines are Picking "Yes":

As you want these sorted ascending based on the Warehouse / Bay Location / Shelf, this will set the order:

If we than lookup the product codes of the table in this order matrix and append the results to the table, this would make:

We can then use this last column in the sort criteria, to keep similar product codes together and still have the picking lines be sorted based on the other criteria.