Formula help.

[serge]

Hi,

I have this formula that work very well but I need some help when the formula return a 0 that the 0 will be at the end ( on the right ) and not at the beginning, thank you.

{=IFERROR(SMALL(IF(ISNUMBER(MATCH(COLUMN($J$6:$N$6)-COLUMN($J$6)+1,MATCH($J6:$N6,$J6:$N6,0),0)),$J6:$N6),COLUMNS($Q$6:Q$6)),"")}

For example if the formula return : 04689 I would need it to return : 46890.

I know it has to do with the " SMALL " but dont know how to change it ?
Serge.

 

[serge]

bosco,

Thank you for the formula it works by putting the "0" at the end, but if you read the linked post, the digit ( 1,2,3 ) should not be returned .

So for example if the digit are : 1 3 0 7 5 the formula should return : 5 7 0

Thank you for your time , I really appreciate it.

 

[bosco_yip]

bosco,

Thank you for the formula it works by putting the "0" at the end, but if you read the linked post, the digit ( 1,2,3 ) should not be returned .

So for example if the digit are : 1 3 0 7 5 the formula should return : 5 7 0

Thank you for your time , I really appreciate it.

If the digit ( 1,2,3 ) should not be returned and putting "0" in the end

then,

In W6, Shift+Ctrl+Enter copied across to AA6 and all copied down :

=IFERROR(0+TEXT(SMALL(IFERROR(FIND($Q6:INDEX($Q6:$U6,MATCH(9^9,$Q6:$U6))," 4567890"),"a"),COLUMN(A1)),"[=10]\0;0"),"")

Regards
Bosco

 

[admiral100]

Hi,

It's not elegant but working

Ctrl+Shift+Enter NOT just Enter

W6 =IFERROR(IF(IFERROR(SMALL(IF(ISNUMBER($Q6:$U6),VALUE(SUBSTITUTE($Q6:$U6,0,10))),COLUMN(A1)),"")=10,0,SMALL(IF(ISNUMBER($Q6:$U6),VALUE(SUBSTITUTE($Q6:$U6,0,10))),COLUMN(A1))),"")

	Q	R	S	T	U	V	W	X	Y	Z	AA
6	0	1	6	7			1	6	7	0
7	0	4	7				4	7	0

<colgroup><col width="70" span="12" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[kvsrinivasamurthy]

Try this

=IFERROR(SMALL(IF(ISNUMBER(MATCH(COLUMN($J$6:$N$6)-COLUMN($J$6)+1,MATCH($J6:$N6,$J6:$N6,0),0)),IF($J6:$N6>0,$J6:$N6,"")),COLUMNS($Q$6:Q$6)),0)

 

[kvsrinivasamurthy]

Try this

=IFERROR(SMALL(IF(ISNUMBER(MATCH(COLUMN($J$6:$N$6)-COLUMN($J$6)+1,MATCH($J6:$N6,$J6:$N6,0),0)),IF($J6:$N6>0,$J6:$N6,"")),COLUMNS($Q$6:Q$6)),0)

It is an ARRAY Formula. It is to be confirmed with Ctrl+Shift+Enter keys together.

 

[serge]

admiral100,

Thank you for your formula, it works fine but the digits 1,2, and 3 show up in the return and they shouldn't !
Otherwise the 0 is at the end like I need it , I just need the formula not to return those 3 first digits ( 1,2,3 ) look in this post :
https://www.mrexcel.com/forum/excel...rmula-do-not-include-1-2-3-a.html#post4996151

Thank you.
Serge.

 

[serge]

Hi bosco,

Thanks for your help, but this formula is not working , it return even digits that are not even there !!!
Serge.

 

[serge]

kvsrinivasamurthy,

It's almost perfect but it return also the digits, 1,2,3 which it shouldn't if you can modified it , that would be it ?

That formula need to return all digits with the "0" at the end ( Right ) minus digits 1,2,3.

so if i have this 5,1,2,0,9,7 it should return : 5,7,9,0.

 

[Marcelo Branco]

Hi Marcelo,


Thank you for responding to me post, sorry about late respons, what Ineed is for the formula to return the "0" at the end of the other digits to the Right, and not on

the front left.


Because I use those digits with some other formulas and when the "0" is on front my other formlas dont recognize it !!


See below in green what I would like the formula to return.


Thank you.
Serge.

	Q	R	S	T	U	V	W	X	Y	Z	AA
6	0	1	6	7	8		6	7	8	0
7	0	4	7	8	9		4	7	8	9	0

<colgroup><col style="width:30px; "><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:64px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Maybe...

P​

Q​

R​

S​

T​

U​

V​

W​

X​

Y​

Z​

AA​

5​

6​

6​

0​

1​

6​

7​

8​

6​

7​

8​

0​

7​

7​

0​

4​

7​

8​

9​

4​

7​

8​

9​

0​

8​

Array formula in W6 copied across and down
=IFERROR(--SUBSTITUTE(SMALL(IF($Q6:$U6<>"",IF(ISNA(MATCH($Q6:$U6,{1;2;3},0)),--SUBSTITUTE($Q6:$U6,0,10))),COLUMNS($W6:W6)),10,0),"")
Ctrl+Shift+Enter

M.

 

[serge]

Perfect, perfect, perfect, thank you Marcelo, that's exactly what I needed, I really appreciate your help, Thank you.
Serge.