Sort ranges of data based on one cell within each range range

[sayoung4517]

I have about 100 part numbers on a spreadsheet. Cell (D721) example. As you can see there about 10 columns and 15 rows of data tied to each part number.

How can I sort the data by part number and keep all the existing cells surrounding the part number intact including the formulas. Fortunately all the formulas reference cells within the table.

Appreciate any help I can get. This one has me stumped.

 

[pecoflyer]

Please post your sheet instead of a picture. Thx

 

[GraH - Guido]

Don't think it can be accomplished with a simple sort. If I understand correctly you want to be able to sort per block of data.
A notorious member here would say a macro can [DO IT]
Personally I would attempt a Power query, but that would mean loosing your formulae in the end result.
I think some formulae based workarounds could work, but providing these formulae would be based on too much assumptions. Pecoflyer is right to ask for a sample workbook.
Perhaps one of these 2 suggestions can trigger you, but both require some rework in the workbook set-up:

1. A workaround could be to sort a single list of part numbers and have the table as shown in the picture being filled in based on the sorted list. Hence the blocks of data get sorted too.
2. Or... add a "sort" column that runs through all the rows and starts a new sequence based on the part number.
   
   • So in L721 you would get a number like 10000, assuming it is the first of part number, and then down 10001, 10015. In L706 and down you could get 30000, 30001,.. 30015.
   • The first 3 digits allow sorting of 999 part number, you have 100
     
   • the last 2 for each of the block rows

If you would need to keep an original order, you can use a second sort column that is filled with ascending numbers.