Sorting order of items in nested table

[lswmalloy]

In Appsheet, I have a table of departments, which links to a table of people in those departments. 

I'm trying to create a report template that lists out the people in each department, grouped by department and sorted by last name.

I want something like:

Department 1

Adams, Joe	Smith, John	Zarha, Tim
Day, Sara	Wright, Julie

Department 2

Andrews, Mary	Rega, Tim	Smith, John
Bendi, Sue

I have sorted by department and then made a nested table to make the three columns.

<<Start:ORDERBY(SELECT([Related People][WID], MOD([_ROWNUMBER], 3)=1), [Last Name], TRUE)>><<[Last Name]>>, <<[First Name]>> <<End>>

<<Start:ORDERBY(SELECT([Related People][WID], MOD([_ROWNUMBER], 3)=2), [Last Name], TRUE)>><<[Last Name]>>, <<[First Name]>> <<End>>

<<Start:ORDERBY(SELECT([Related People][WID], MOD([_ROWNUMBER], 3)=0), [Last Name], TRUE)>><<[Last Name]>>, <<[First Name]>> <<End>>

This sorts by the row number of the People table, not the last name. Since the people were not added in alphabetical order I end up with something like this. 

Department 1

Smith, John	Zarha, Tim	Adams, Joe
Wright, Julie		Day, Sara

Department 2

Smith, John	Andrews, Mary	Rega, Tim
	Bendi, Sue

All the people are in the right department, but not filling out the columns evenly (and therefore not sorted by last name appropriately).

Is there a way to create a virtual 'rank' column that I could use to create the table layout instead of the row number? Or any other ideas?

Ideally, the names would be sorted first vertically, then horizontally, but I would be alright with horizontally then vertically.

Any help would be appreciated!

[Steve · Accepted solution]

I'm thinking you'd have to use an HTML template rather than a Docs template. See also: Part 1 - Almost pixel perfect pdf reports from automation

[TeeSee1]

Using this INDEXOF() technique authored by @Steve , you can achieve a result close to what you want using a Google Doc template. If you prefer more precise formatting, I believe you need to explore a solution using an HTML template as @Steve suggested.

Template used:

<<Start:ORDERBY(SELECT(ITEMS[id],AND(MOD(COUNT(SPLIT(ANY(SPLIT( (" , " & ORDERBY( [_ThisROw-1].[Related ITEMSs], [item_name]) & " , "), (" , " & [id] & " , ")  )  ),    " , "  )),3) = 1,[category]=[_THISROW-1].[id])),[item_name])>>
<<[item_name]>>
<<End>>

Result:

The data used is simply a list of ITEMS which has a category reference, which I believe basically has the same structure as your data.

[lswmalloy]

Thank you!  This works for what I asked for, and you are correct that an HTML template is best for what I am actually ultimately looking for.