0

Give a sequence of columns, I want to sort each column individually using formula because of some reason.
Eg.

A B C D
4 7 2 5
1 8 1 3
9 3 4
7 4

Expected output

A B C D
1 7 1 3
4 7 2 4
8 3 4
9 5

Is there any way to do that with a formula? Also the formula should take the whole range as input like A1:D4. Any help is appreciated

Edit - My real problem is solving a trick question, it is not related to any real-world dataset. I saw this trick question answered on youtube here - link. While the solution is provided for a single cell and dragged for remaining cells, I wanted to implement it using ArrayFormula for the whole column so that I don't have to keep dragging it again.

I don't know if it is possible or not, or is it valid to solve questions like these in google sheets. Do tell if it's not appropriate in tools like sheets.

5
  • Hmm... is this an XY problem? May 25, 2022 at 13:24
  • @doubleunary: sorry for the late response, my work immediately changed from sheets to other things. Yes it as an XY problem, actually problem was sort a number like - 7865 to 5678, I have the formula for a single cell but I wanted to expand it to the whole column that's why I was trying various things, this is the video I referred to for a single cell sort - link
    – Dhruv
    Jun 4, 2022 at 12:42
  • Please edit the question so that it tells what your end goal is. Show realistic-looking source data and also show your expected results. Jun 4, 2022 at 13:27
  • @doubleunary, I've added my real aim, although it is not for any real world dataset but a trick question instead
    – Dhruv
    Jun 5, 2022 at 7:15
  • I prefer to use my time helping people with real-world problems. It sounds like you are trying to solve this for fun. You will get more fun out of the problem if you solve it yourself. Jun 5, 2022 at 17:04

1 Answer 1

0

If I understand you correctly this formula should solve:

={SORT(A1:A,1,true),SORT(B1:B,1,true),SORT(C1:C,1,true),SORT(D1:D,1,true)}

enter image description here

EDIT: this formula is based on your last request:

=byrow(A1:A,lambda(r,if(r="",,join("",sort(transpose(split(regexreplace(r&"","(?s)(.{1})","$1 ")," ")))))))

all the strings (number and/or text) contained in the cells of column A will be sorted individually

3
  • Yeah but I want something that takes the whole range as input like A2:D5, could you make it like that?
    – Dhruv
    May 25, 2022 at 2:40
  • see my edited answer
    – Daniele
    Nov 2, 2022 at 17:14
  • are you sure this works? It didn't work for me. Also, I am trying to give the whole range as input i.e. all columns at once.
    – Dhruv
    Nov 25, 2022 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.