1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by sayoung4517, Apr 11, 2018.

  1. sayoung4517

    sayoung4517 New Member

    Messages:
    11
    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.


    upload_2018-4-10_18-3-34.png
  2. pecoflyer

    pecoflyer Active Member

    Messages:
    151
    Please post your sheet instead of a picture. Thx
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    521
    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.

Share This Page