Please require a formula modification or a VBA alternatively

[motilulla]

Hello,</SPAN></SPAN>

Previously I had data as shown in the row 6 and formula in cell AV1 and fill to right and down (this formula extract only unique value in ascending order left to right)</SPAN></SPAN>

But recent data has been changed as shown in the row 9 where red font data has been added to after each of the original data so the formula is not working... </SPAN></SPAN>

Because I want the same results ignoring added data. Please so far need a new formula for cell AV9 or a VBA alternatively solution to get same results as per row 6 data... </SPAN></SPAN>

Here is an example....</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
1
2
3
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9							n1	n2	n3	n4	n5	n6	n7	n8	n9							n1	n2	n3	n4	n5	n6	n7	n8	n9							n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14	n15	n16	n17	n18	n19
6			10	4	2	1	7	3	6	0	8							11	12	8	5	10	0	2	4	24							5	1	0	3	22	38	47	4	2							0	1	2	3	4	5	6	7	8	10	11	12	22	24	38	47
7
8
9			10	4	2	1	7	3	6	0	8	41	39	49	10	-2		11	12	8	5	10	0	2	4	24	76	60	84	24	8		5	1	0	3	22	38	47	4	2	122	83	130	47	-11		?
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
AV6	=IF(MAX($C6:$AT6)=MAX($AU6:AU6),"",SMALL($C6:$AT6,COUNTIF($C6:$AT6,"<="&AU6)+1))

Thank you all</SPAN></SPAN>

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

 

[DanteAmor]

Try the following array formula, check if the column in row 5 is different from space, that is, only consider the number x9 if it has n1, n2, n3, netc in the cell x5.

{=IF(MAX(IF($C5:$AT5<>"",$C9:$AT9))=MAX($AU9:AU9),"",SMALL(IF($C5:$AT5<>"",$C9:$AT9),COUNTIFS($C9:$AT9,"<="&AU9,$C5:$AT5,"<>" & "")+1))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[motilulla]

Try the following array formula, check if the column in row 5 is different from space, that is, only consider the number x9 if it has n1, n2, n3, netc in the cell x5.

{=IF(MAX(IF($C5:$AT5<>"",$C9:$AT9))=MAX($AU9:AU9),"",SMALL(IF($C5:$AT5<>"",$C9:$AT9),COUNTIFS($C9:$AT9,"<="&AU9,$C5:$AT5,"<>" & "")+1))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Hello DanteAmor, I think "COUNTIFS" function in the formula does not support my version. Please can you take a look? </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[DanteAmor]

I think "COUNTIFS" function in the formula does not support my version.

What version of Excel do you have?
What appears as a result in the formula.
You are pressing Shift + Control + Enter to accept the Array formula.

 

[motilulla]

What version of Excel do you have?
What appears as a result in the formula.
You are pressing Shift + Control + Enter to accept the Array formula.

Hello DanteAmor, i am using Excel 2000 which I have mention in the post#1 bottom of the left corner I am getting result #¡VALUE!
Yes I entered the formula by pressing the </SPAN></SPAN>Shift + Control + Enter</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello DanteAmor, if it could work changing the header any instead n1, n2, n3.... and so can be it only number like 1, 2, and 3.... or if formula works you can suggest any.... I can change it no problem </SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[DanteAmor]

Countif, has a peculiarity, which does not add if n < "", but I think I found an alternative.

Try this


{=IF(MAX(IF($C5:$AT5<>"",$C9:$AT9))=MAX($AU9:AU9),"",SMALL(IF($C5:$AT5<>"",$C9:$AT9),IF(AU9="",0,SUM(IF(($C9:$AT9<=AU9)*($C5:$AT5<>""),1)))+1))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[motilulla]

Countif, has a peculiarity, which does not add if n < "", but I think I found an alternative.

Try this


{=IF(MAX(IF($C5:$AT5<>"",$C9:$AT9))=MAX($AU9:AU9),"",SMALL(IF($C5:$AT5<>"",$C9:$AT9),IF(AU9="",0,SUM(IF(($C9:$AT9<=AU9)*($C5:$AT5<>""),1)))+1))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Spot on! DanteAmor, the formula worked like a dream absolutely fantastic! </SPAN></SPAN>

I appreciate your kind help and time for solving it. </SPAN></SPAN>
Good Luck </SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.