CODEWITHSUNDEEP
excelsortingexcel-formulaoutput
SpringIntoJava
SpringIntoJava

Reputation: 45

How to output excel sorting result into separate columns by content

I have an excel column which I want to sort and output the result into a separate sheet. I want the output to be sorted into separate columns, so that each column in the output sheet should have its own type of content, only as many times as it showed up in the original.

e.g. if the original column looks like this:

Right
Left
Left
Right
Right

I want the next sheet to show:

Right    Left
Right    Left
Right

Note, that I Don't want to specify what I am sorting according to, i.e. my formula will not contain "enter in this column any content that is equal to 'right'", but rather will be dynamic and will simply group whatever strings are identical into one column, etc.

How do I sort into multiple columns by content and output in a separate sheet?

Upvotes: 0

Views: 306

Answers (1)

Gary's Student
Gary's Student

Reputation: 96771

For Excel 365

Say the first sheet is like:

enter image description here

In A1 of the second sheet enter:

=TRANSPOSE(UNIQUE(Sheet1!A1:A11))

(it will spill across)

In A2 enter:

=FILTER(Sheet1!$A1:$A11,Sheet1!$A1:$A11=A1)

and copy across.

Your required output starts in the second row of this second sheet.

enter image description here

EDIT#1:

If you want the column of "left"s to appear first, then use:

=TRANSPOSE(SORT(UNIQUE(Sheet1!A1:A11),,1))

in A1 of the second sheet.

Upvotes: 1

Related Questions