I’ve created a data set which displays a paired set of values (Name and Unit #). To make everything appear nicely, the data set is formatted such that there are 4 pairs of data per row and 50 rows for a total of 200 pairs of data. A row looks something like this:
| Name | Unit # | Name | Unit # | Name | Unit # | Name | Unit # |
Initially I set this up ordered by Unit # (1-50 in column 2, 51-100 in column 4, etc). This seemed like the simplest solution so that data would not need to be reordered when a vacancy occurs. In that case, a name would just need to be removed and the 50 row by 8 column structure is preserved because the Unit # remains in position.
The problem is, I now need to rearrange the data alphabetically by Name rather than by Unit #. Since the dataset is setup to have multiple column pairs, I can’t just sort the data by column 1 (since this would put columns 3-8 out of order). I also need to make sure the columns never have more than 50 rows, so adding a data pair to a column pair would need to shift the last pair to the next column (and removing one would need to do the reverse).
The best solution I’ve devised so far is the following:
- Download the Dataset as a CSV
- Open in Excel
- Remove the ID field.
- Add or remove data pairs as required
- Sort the each of the for column pairs individually
- Determine if any of the column pairs have more of less than 50 data pairs and manually adjust until all column pairs have 50 data pairs.
- Save to CSV
- Import the CSV choosing the “Overwrite existing data” and “Ignore first row” options.
As I won’t be the person ultimately making the updates, I’m looking for a simpler process. Is there a better way to accomplish this? Is there a Building Directory template that I should be using that can handle sorting and overflow of the data all at once?
Any suggestions are welcome. Thanks