-1

Say I have multiple columns:

A    | B    | C…
Bill | Bill | John
Adam | Fran | Nick
     | Adam2| Adam

I’d like to alpha sort the rows in each column, and also add empty values in a cell if the cell does not have the same value as its row-sibling cells. The result would look like:

A    | B     | C…
Adam |       | Adam
     | Adam2 |
Bill | Bill  | 
     | Fran  |
     |       | John
     |       | Nick
Improve this question
3
  • And what have you tried so far to accomplish your ask? If you want to do the sort in the same table, then you'll need VBA, if the output can be a different table then, probably power query, or some type of lambda formula.
    – gns100
    Commented Jan 15 at 16:38
  • I’m not familiar enough with Excel/Sheets to even know what to mess around with, tbh. I’ve considered doing this in Python (Pandas or something similar), which is more familiar to me, but I’m trying to share this with non-programmers. I was hoping there was some magical function native to Sheets/Excel that could do this already.
    – Matt
    Commented Jan 16 at 0:47
  • Questions about "google-spreadsheets" are off-topic because it's a web app. The question includes nothing about Google Sheets; google-spreadsheets should be removed.
    – Wicket
    Commented Jan 16 at 3:32

1 Answer 1

Reset to default
1

In Excel 365 you can use formula like this:

=LET(clr,TRIM($A$2:$C$4), wbl,SORT(UNIQUE(TOCOL(clr))),
 list,FILTER(wbl,wbl<>""), MAKEARRAY(ROWS(list),COLUMNS(clr),
 LAMBDA(r,c,LET(col,CHOOSECOLS(clr,c),a,INDEX(list,r),
 IF(ISERROR(MATCH(a,col,0)),"",a)))))

Sort3cols

In place of $A$2:$C$4 you put the address of your data range. You can also convert this formula into LAMBDA with one argument.

Improve this answer
2
  • This can be shorten by adding optional ignore blanks modifer in the TOCOL() formula.
    – gns100
    Commented Jan 16 at 22:23
  • Thank you for your comment. I don't know if I understood your intention correctly, but I think the possibility of shortening this formula depends on the form of the data. The TOCOL function with optional parameter 1 allows you to skip empty cells, but not empty texts. The data prepared by the OP contained spaces that had to be filtered out, resulting in empty texts.
    – MGonet
    Commented Jan 16 at 23:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .