Formula help.

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,399
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Last edited:
Upvote 0
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
QRSTUVWXYZAA
6016786780
70478947890
Sheet
 
Upvote 0
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.
 
Upvote 0
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))),"")


QRSTUVWXYZAA
601678 16780
704789 47890

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

W6, copied across and down :

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

Regards
Bosco
 
Upvote 0
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 ?
 
Upvote 0
bosco,

Thank you for your time but the formula doesn't work ! it return : #NAME ? when I put it in W6.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top