0

Let me get this straight. I have the following data:

Initial data

I want to sort it base on "x", the result should be

Sorted data

the row that contains more "x" and has the first "x" to the 10th will be at the top, it's like sorting from large to small amount of "x".

how can I possibly do that?

I know how to sort one column base on integers and a-z vice-versa but this one is different for me.

1
  • 1
    Add a helper column that contains a count of the Xs, then sort on that. Is there a rule for when multiple rows have the same count?
    – fixer1234
    Apr 24 '18 at 7:43
3

In column M put a title, for example COUNT. In cell M2 put the formula =COUNTIF(C2:L2,"X") and copy down the formula through the column.

Now, in column M you have the count of X in the row.

In column N put a title, for example SUM. In cell N2 put the formula =SUMIF(C2:L2;"X";$C$1:L1) and copy down the formula through the column.

Now, in column N you have the WEIGHT of the X's for each row.

Go to sort and in the dialog choose COUNT Largest to Smallest and another level with the conditions SUM or whatever name you put in column N and Smallest to Largest, like this:

enter image description here

5
  • It works, but there's a problem. as you can see, the column "x" is from 1 to 10. I want to prioritize those columns that contains "x" from 1 to 10. e.g. C:1 from row:2 has only one "x" and c:2 and c:3 from row:2 contains "x", so the row:1 will be prioritize to be at the top of row:2 because c:1 has the "x". Apr 24 '18 at 9:01
  • @KrixMuxDeadpool I corrected my answer to help you with the condition
    – jcbermu
    Apr 24 '18 at 10:06
  • I Got a problem with your formula =SUMIF(C2:L2;"X";$C$1:L1) please check this link to see the screenshot lh3.googleusercontent.com/… Apr 25 '18 at 6:22
  • I'm confused with what you said Go to sort and in the dialog choose COUNT Largest to Smallest and another level with the conditions SUM or whatever name you put in column N and Smallest to Largest, what do you mean by another level with the conditions SUM? did you mean, sort the value of column COUNT from Largest to Smallest and also the column SUM? Apr 25 '18 at 6:37
  • @KrixMuxDeadpool Check the image that I added to the answer
    – jcbermu
    Apr 25 '18 at 7:40
2

Create a helper column. For example, in M2 enter =Countif(C2:L2,"x") and then fill down. Now sort by Column M.

0

You might be willing to try the following:

  1. In column M, prepare a "helper column" which counts the number of x's as described in previous answers.
  2. In column N, please enter =MATCH("x", C2:L2, 0) which will give you the position of the first x in each row. Drag down to extend formula to all rows involved and/or adapt suitably to your locale.
  3. Now, you may order by two levels: first, column M, descending; second, column N, ascending.

Hope this fits your needs.

Kind regards.

-1

You need a Helper Column to Sort the Data on basis of most columns filled per row.

Write this formula in M2 =COUNTA(C2:L2) & fill it down till row 6.

Then follow these steps:

  • Select A1:M6.
  • Click Sort & Filter.
  • Click Custom Sort.
  • Select Helper Column, Values for Sort on and Smallest to Largets As order.
  • Finally Click Ok. enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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