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.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Marcelo Branco]

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

Serge,

The formula is not correct - i wrongly used the range $Q6:$U6 that does not include the value in column P.

Try this new version
W6 copied across and down
=IFERROR(--SUBSTITUTE(SMALL(IF($P6:$U6<>"",IF(MATCH($P6:$U6,$P6:$U6,0)=COLUMN($P6:$U6)-COLUMN($P6)+1,IF(ISNA(MATCH($P6:$U6,{1;2;3},0)),--SUBSTITUTE($P6:$U6,0,10)))),COLUMNS($W6:W6)),10,0),"")
Ctrl+Shift+Enter

M.

 

[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>3,$J6:$N6,"")),COLUMNS($Q$6:Q$6)),0)

 

[serge]

Thank you Marcelo,

I changed it, but it seems to work fine with the previous one ! Thank you very much for the help everything works fine, you are the best.

Serge.

 

[serge]

kvsrinivasamurthy,

Thank you for your help, I appreciate it.

Serge.