SOLVED

sorting on sub-fields

Copper Contributor

I have data that has multiple columns, one is a data column with fields like:

 

Fall 2020

Spring 2021

 

I want to make a table of unique dates, and summarize other various attributes by this (=semesters).

 

So I massaged it to get by year first - "2021 Fall", "2020 Spring", etc.

but sorting on that is wrong, as Fall is alphabetically before Spring, so

 

Fall 2020 < Spring 2021

 

Conceptually I want to sort first on descending Spring/Fall portion, then on ascending year portion.

 

I did then add a new column where I convert these to actual dates, so I can sort on those, but I want to have the summary table list by unique on semester date labels.

 

I did try to do a sort on the range including the semester labels and the actual dates, sorting on the date column, but the result of that is then multiple columns, and I don't see how to subset it to then get unique on just the semester labels.

 

    =UNIQUE(SORT((Data Term):(Data StartDate),3))

 

The Unique considers all 3 columns in its scan and result, I only  want it to use the (now properly) sorted first column.

 

If there was a function to select only one column from this result, and unique on it - that would work.

=UNIQUE(select(1,SORT((Data Term):(Data StartDate),3)))

 

So I can do it by creating some intermediate columns, but would like something simpler, and without needing these additional steps.

1 Reply
best response confirmed by Gregory Guthrie (Copper Contributor)
Solution

@Gregory Guthrie 

Not sure I understood the logic. Let me illustrate on the sample

image.png

We would like take unique values in first column, sort them and return for the values for other columns.

 

That could be like

=INDEX(Table1, XMATCH( SORT( UNIQUE(Table1[A]) ), Table1[A]), SEQUENCE( , COLUMNS(Table1) ) )

Or it shall be something else?

1 best response

Accepted Solutions
best response confirmed by Gregory Guthrie (Copper Contributor)
Solution

@Gregory Guthrie 

Not sure I understood the logic. Let me illustrate on the sample

image.png

We would like take unique values in first column, sort them and return for the values for other columns.

 

That could be like

=INDEX(Table1, XMATCH( SORT( UNIQUE(Table1[A]) ), Table1[A]), SEQUENCE( , COLUMNS(Table1) ) )

Or it shall be something else?

View solution in original post