Count all values of each row from smaller to larger

[motilulla]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5				n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14		Count Smaller to Larger
6
7				1	0	0	0	1	0	1	0	1	1	0	0	1	0		6 | 8
8				2	1	1	0	2	1	0	0	2	0	1	1	0	0		6 | 5 | 3
9				3	0	0	0	3	0	1	0	3	1	0	0	1	0		8 | 3 | 3
10				4	0	0	1	4	0	2	0	0	2	1	0	0	0		8 | 2 | 2 | 2
11				0	1	0	2	0	0	3	0	0	3	2	1	0	0		8 | 2 | 2 | 2
12				1	2	1	3	1	1	0	0	1	0	0	0	0	1		6 | 6 | 1 | 1
13				2	0	0	4	0	0	0	1	0	0	1	0	1	0		9 | 3 | 1
14				0	1	1	5	0	1	0	0	0	1	0	0	2	0		8 | 4 | 1 | 1
15				0	0	0	6	1	2	1	0	1	2	0	1	3	1		5 | 5 | 2 | 1 | 1
16				0	2	0	1	1	0	1	0	0	0	0	0	0	0		10 | 3 | 1
17				1	0	1	0	2	0	2	0	1	1	1	1	1	0		5 | 7 | 2
18				2	0	0	0	0	1	0	0	0	0	0	0	0	1		11 | 2
19				0	0	0	0	6	0	1	5	0	2	6	0	1	3		7 | 2 | 1 | 1 | 1 | 2
20				1	1	1	0	7	0	2	6	0	3	0	1	2	0		5 | 4 | 2 | 1 | 1 | 1
21				0	0	2	0	0	0	0	1	0	0	4	0	0	0		11 | 1 | 1 | 1
22				0	0	1	0	0	0	0	0	0	0	0	2	1	0		11 | 2 | 1
23				0	1	2	1	1	1	0	1	1	1	1	0	2	0		4 | 8 | 2 |
24				1	2	3	2	2	2	0	0	0	2	0	1	0	0		6 | 2 | 5 | 1
25				0	3	4	0	3	3	1	0	1	0	0	0	1	1		6 | 4 | 3 | 1
26				1	0	5	0	4	0	2	1	0	1	0	0	2	0		7 | 3 | 2 | 1 | 1
27				2	1	0	0	0	1	3	2	1	0	1	1	0	0		6 | 5 | 2 | 1
28				0	0	1	1	1	0	4	0	2	0	2	2	1	0		6 | 4 | 3 | 1
Sheet1

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>
</SPAN></SPAN>

 

[motilulla]

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>

 

[XOR LX]

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).

 

[XOR LX]

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

 

[motilulla]

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>

 

[motilulla]

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

 

[XOR LX]

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

 

[motilulla]

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 ?

 

[Fluff]

NPV is available in 2003, but don't know about 2000
However IFERROR is not available in 2000

 

[XOR LX]

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