Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Count all values of each row from smaller to larger

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Count all values of each row from smaller to larger

    Hello,

    I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible?

    As per example below

    ABCDEFGHIJKLMNOPQRS
    1
    2
    3
    4
    5n1n2n3n4n5n6n7n8n9n10n11n12n13n14Count Smaller to Larger
    6
    7100010101100106 | 8
    8211021002011006 | 5 | 3
    9300030103100108 | 3 | 3
    10400140200210008 | 2 | 2 | 2
    11010200300321008 | 2 | 2 | 2
    12121311001000016 | 6 | 1 | 1
    13200400010010109 | 3 | 1
    14011501000100208 | 4 | 1 | 1
    15000612101201315 | 5 | 2 | 1 | 1
    160201101000000010 | 3 | 1
    17101020201111105 | 7 | 2
    182000010000000111 | 2
    19000060150260137 | 2 | 1 | 1 | 1 | 2
    20111070260301205 | 4 | 2 | 1 | 1 | 1
    210020000100400011 | 1 | 1 | 1
    220010000000021011 | 2 | 1
    23012111011110204 | 8 | 2 |
    24123222000201006 | 2 | 5 | 1
    25034033101000116 | 4 | 3 | 1
    26105040210100207 | 3 | 2 | 1 | 1
    27210001321011006 | 5 | 2 | 1
    28001110402022106 | 4 | 3 | 1

    Sheet1





    Thank you all

    Excel 2000
    Regards,
    Moti
    Last edited by motilulla; Jan 2nd, 2019 at 08:47 PM.

  2. #2
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by motilulla View Post
    Hello,
    I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible?
    Here is clarification what I mean "from smaller to larger valve" count 1st 0, then 1, then 2, then 3, and so on....

    For example row 7 ranges D7:Q7.........0=6, 1=8...............................so far result S7= 6 | 8

    For example row 8 ranges D8:Q8.........0=6, 1=5, 2=3.......................so far result S8= 6 | 5 | 3
    For example row 9 ranges D9:Q9.........0=8, 1=3, 3=3.......................so far result S9= 8 | 3 | 3
    For example row 10 ranges D10:Q10....0=8, 1=2, 2=2, 4=2.............so far result S10= 8 | 2 | 2 | 2

    May this help


    Regards,

    Moti
    Last edited by motilulla; Jan 3rd, 2019 at 08:31 AM.

  3. #3
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Hi,

    I don't agree with your results for rows 7, 13 and 18, but try this array formula** in S7:

    =SUBSTITUTE(TEXT(NPV(-0.9,IFERROR(1/(1/COUNTIF(D7:Q7,{9,8,7,6,5,4,3,2,1,0}))/10,"")),REPT("# | ",COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))," | ","",COUNT(1/FREQUENCY(D7:Q7,D7:Q7)))

    and copied down.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    N.B. I should've mentioned that I presumed from your data that the only possible entries in a given row are the single-digit numbers from 0-9.

    Regards
    Last edited by XOR LX; Jan 3rd, 2019 at 09:15 AM.
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by XOR LX View Post
    Hi,
    I don't agree with your results for rows 7, 13 and 18, but try this array formula** in S7:
    Hello XOR LX, sorry you are correct row 7 should be = 8 | 6, row 13 should be = 9 | 3 | 1 | 1 and row 18 should be = 11 | 2 | 1.

    Thank you for help and giving a formula solution but it is not working with my version Excel 2000. May it is good for earlier versions


    Kind Regards,

    Moti


  6. #6
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by XOR LX View Post
    N.B. I should've mentioned that I presumed from your data that the only possible entries in a given row are the single-digit numbers from 0-9.

    Regards
    XOR LX, in this given example there are only "single-digit numbers from 0-9" but it could be from 0-99
    Last edited by motilulla; Jan 3rd, 2019 at 09:29 AM.

  7. #7
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    What results do you get? Are you getting the #NAME ? error? If so, can you confirm that it is the NPV function which is not available in your version of Excel?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by XOR LX View Post
    What results do you get? Are you getting the #NAME ? error? If so, can you confirm that it is the NPV function which is not available in your version of Excel?

    Regards
    I am getting #NAME Please can you guide me how to check where I can find NPV ?
    Last edited by motilulla; Jan 3rd, 2019 at 09:33 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,061
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    NPV is available in 2003, but don't know about 2000
    However IFERROR is not available in 2000
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by motilulla View Post
    I am getting #NAME Please can you guide me how to check where I can find NPV ?
    My mistake. Of course it's the IFERROR (and potentially also NPV, though I thought that was available in 2000), which is causing the error.

    Quote Originally Posted by motilulla View Post
    XOR LX, in this given example there are only "single-digit numbers from 0-9" but it could be from 0-99
    Ah, in that case unfortunately this will not be possible using worksheet formulas alone with your version of Excel. You'll require some VBA. Are you ok with that?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •