Sorting column groups having shared rows?

I would separate storage from display. Keep one master table with four columns only: Call, Name, State, and Notes. Add every participant to the bottom of that master table, then sort that table by Call. On a second sheet, build the one-screen view from the sorted master list so Excel can wrap the records across the page without changing the underlying data. In Microsoft 365, the display sheet can use SORT to order the master data and WRAPROWS or a similar layout formula to split it into four-column groups. That way you never sort only part of a row and accidentally detach a call sign from the name or state. The master list stays boring, which is exactly what makes it reliable.

Hi Virago​,

In cell F1 I have applied this formula (see the screenshot):

=LET(
     data, A1:.D200,
      arr, ARRAYTOTEXT,
    w_col, WRAPCOLS(BYROW(DROP(data, 1), arr) & ", ", 12),
           DROP(VSTACK(TEXTSPLIT(arr(TOROW(IFNA(EXPAND(BYROW(TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,
                       REPT(TAKE(data, 1) & ",", COLUMNS(w_col))), ",", ";", TRUE)), arr),, 2), ""))), ", "),
                  TEXTSPLIT(TEXTJOIN(";",, BYROW(w_col, arr)), ", ", ";")),, -1)
)

Try it and let me know.

IlirU

Gentlemen,

My heartfelt "THANKS!" to all of you! Your proposals open uncharted possibilities which I'm sure I'll enjoy exploring! I've previously used the idea of a separate data sheet from which to construct a desired display, but not using the codes/commands offered in your suggestions. I look forward to my next rainy day with a copious supply of fresh black coffee!!!

I'm honored to have contributed the raw data that @Patrick2788 used to demonstrate his very powerful and elegant solution, seriously. I still have much to learn,

A note to Virago​ regarding Patrick's formula. He entered it as =WrapRows2Dλ(data,10) .. you can change the number of rows in each extracted subset by changing that final number from 10 to, say, 12 -- or any other number that lets you keep a growing database all visible on one output screen. You will need to follow the advice from m_tarler​ and me to sort the database and keep it separate from the output section. Let the WrapRows function take care of the display.

I have a function in my Lambda library for this situation.

The sheet level formula is very simple:

=WrapRows2Dλ(data,10)

I've attached a workbook that includes access to the function and plenty more.

I'm sure one of the true wizards here will have a more elegant solution than this, but this will do for starters. In the attached, I used part of a downloaded file showing prices for some stocks and options. In the future, you could help us help you by attaching not an image but an actual Excel database. It would also be prudent not to use real call signs and names, as I suspect you have done here.

First, I would separate your raw database from this "dashboard" or output display. (In general, separating input (and a resulting database) from output is a good idea because it helps avoid the problem you're facing.) Do your sort of the entire database before extracting the data. On my sample file, you can change the sort to see how the display immediately reflects the new order.

Then avail  yourself of one or more of the Dynamic Array functions. In this case--and this is admittedly kludgy--I just chose the TAKE function and set some cell references in the top row, taking advantage of the INDIRECT function to give the TAKE function its first row. 

Note: If you've never used Dynamic Array functions before, one thing you need to be aware of is this: a single formula produces an array. In this case, I've only entered a formula in three cells (H3, L3 and P3). 

Have fun: I'll play with it some more to come up with something--I'm sure it's possible--where a single dynamic array function would produce your entire desired array.

Yes.  First I would keep 1 tab as the master list.  Then on a separate tab I would have that header an in cell A3 post:

=LET(data, SORT(DataTable), r, ROUNDUP(ROWS(data)/4,0),
         IFERROR(HSTACK(CHOOSEROWS(data,SEQUENCE(r)),"",
                                      CHOOSEROWS(data,SEQUENCE(r,,r+1)),"", 
                                      CHOOSEROWS(data,SEQUENCE(r,,2*r+1)),"", 
                                      CHOOSEROWS(data, SEQUENCE(ROWS(data)-3*r,,3*r+1))),
                         "")
          )

where DataTable is a reference to that master list (I recommend making that list 'Format as a Table' and name that table accordingly so that named range will automatically expand as you add names.

for a more generalized LAMBDA function to do this, Patrick2788​ just posted a set of library functions including a reshaping grid function that should handle this well.

EDIT.. a) I realize I forgot the sort function so I added it to the function above (that way you don't have to worry if you sorted the original list.  And b) w/r to Patrick's function: =WrapRows2Dλ(data,10) I might recommend a slight variant: =WrapRows2Dλ(data,ROUNDUP(ROWS(data)/4,0) ) so it will always produce the 4 columns you wanted.