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]

Hi Serge,

As far as I can understand, the formula, considering only unique values in J6:N6, returns the smallest value in Q6; the second smallest in R6 and so on.
What do you need? A data sample along with the expected results would be useful to better understand your question.

M.

 

[serge]

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.

Excel Workbook
	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
Sheet

 

[Marcelo Branco]

Serge

It's not clear what the formula should do.
Questions
Why the 1 in R6 doesn't appear in W6:AA6?
You formula in post 1 refers to the range J6:N6 and the data in your last post refer to different ranges.

M.

 

[serge]

Hi Marcelo,

Here is the link for you to understand the formula.
https://www.mrexcel.com/forum/excel...rmula-do-not-include-1-2-3-a.html#post4996151

In Cell W:AA is to show you what I need the formula to return but in K:O
All I need is for the "0" to be at the end ( right ) instead of the front of the digits.

Thank you.
Serge.

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

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


[TABLE="width: 840"]
<colgroup><col width="70" span="12" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]Q[/TD]
[TD="class: xl64, width: 70"]R[/TD]
[TD="class: xl64, width: 70"]S[/TD]
[TD="class: xl64, width: 70"]T[/TD]
[TD="class: xl64, width: 70"]U[/TD]
[TD="class: xl64, width: 70"]V[/TD]
[TD="class: xl64, width: 70"]W[/TD]
[TD="class: xl64, width: 70"]X[/TD]
[TD="class: xl64, width: 70"]Y[/TD]
[TD="class: xl64, width: 70"]Z[/TD]
[TD="class: xl64, width: 70"]AA[/TD]
[/TR]
[TR]
[TD="class: xl64"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl63, width: 70"]1[/TD]
[TD="class: xl63, width: 70"]6[/TD]
[TD="class: xl63, width: 70"]7[/TD]
[TD="class: xl63, width: 70"]8[/TD]
[TD="class: xl63, width: 70"]0[/TD]
[/TR]
[TR]
[TD="class: xl64"]7[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl63, width: 70"]4[/TD]
[TD="class: xl63, width: 70"]7[/TD]
[TD="class: xl63, width: 70"]8[/TD]
[TD="class: xl63, width: 70"]9[/TD]
[TD="class: xl63, width: 70"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[bosco_yip]

Or……

W6, copied across and down :

=0+TEXT(AGGREGATE(15,6,FIND($Q6:$U6,1234567890),COLUMN(A1)),"[=10]\0;0")

Regards
Bosco

 

[serge]

Thank you admiral for the formula, it works fine when there is 5 digits in those 5 cells Q:U, but it return a blank row when I only have 4 or 3 or 2 digits in those rows Q:U ?

 

[serge]

bosco,

Thank you for your time but the formula doesn't work ! it return : #NAME ? when I put it in W6.

 

[bosco_yip]

bosco,

Thank you for your time but the formula doesn't work ! it return : #NAME ? when I put it in W6.

AGGREGATE is a new function introduced since Excel 2010

For the old version Excel 2007 or below, you can use SMALL+IF () instead.

Then, try this revised array formula,

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

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

Regards
Bosco