Count all values of each row from smaller to larger

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,</SPAN>

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?</SPAN></SPAN>

As per example below</SPAN></SPAN>


Book1
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</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>
</SPAN></SPAN>
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hello,</SPAN>
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?</SPAN></SPAN>
Here is clarification what I mean "from smaller to larger valve" count 1st 0, then 1, then 2, then 3, and so on....</SPAN></SPAN>

For example row 7 ranges D7:Q7.........0=6, 1=8...............................so far result S7= 6 | 8
</SPAN></SPAN>
For example row 8 ranges D8:Q8.........0=6, 1=5, 2=3.......................so far result S8= 6 | 5 | 3</SPAN></SPAN>
</SPAN></SPAN>For example row 9 ranges D9:Q9.........0=8, 1=3, 3=3.......................so far result S9= 8 | 3 | 3</SPAN></SPAN>
For example row 10 ranges D10:Q10....0=8, 1=2, 2=2, 4=2.............so far result S10= 8 | 2 | 2 | 2</SPAN></SPAN></SPAN></SPAN>

May this help
</SPAN></SPAN>

Regards,
</SPAN>
Moti
</SPAN></SPAN>
 
Last edited:
Upvote 0
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).
 
Upvote 0
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:
Upvote 0
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.</SPAN></SPAN>

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
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
NPV is available in 2003, but don't know about 2000
However IFERROR is not available in 2000
 
Upvote 0
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.

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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top