Formula help.

serge

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,444
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:
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
serge said:
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.
Click to expand...

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
 
Upvote 0
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))),"")

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

 
Upvote 0
Try this

Code: 
=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)
 
Upvote 0
Hi bosco,

Thanks for your help, but this formula is not working , it return even digits that are not even there !!!
Serge.
 
Upvote 0
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.
 
Upvote 0
serge said:
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.


QRSTUVWXYZAA



<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] "]
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99ffcc]#99ffcc[/URL] , align: center"]0[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
P​
[/td][td="bgcolor: #DCE6F1"]
Q​
[/td][td="bgcolor: #DCE6F1"]
R​
[/td][td="bgcolor: #DCE6F1"]
S​
[/td][td="bgcolor: #DCE6F1"]
T​
[/td][td="bgcolor: #DCE6F1"]
U​
[/td][td="bgcolor: #DCE6F1"]
V​
[/td][td="bgcolor: #DCE6F1"]
W​
[/td][td="bgcolor: #DCE6F1"]
X​
[/td][td="bgcolor: #DCE6F1"]
Y​
[/td][td="bgcolor: #DCE6F1"]
Z​
[/td][td="bgcolor: #DCE6F1"]
AA​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
6​
[/td][td]
0​
[/td][td]
1​
[/td][td]
6​
[/td][td]
7​
[/td][td]
8​
[/td][td][/td][td]
6​
[/td][td]
7​
[/td][td]
8​
[/td][td]
0​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
7​
[/td][td]
0​
[/td][td]
4​
[/td][td]
7​
[/td][td]
8​
[/td][td]
9​
[/td][td][/td][td]
4​
[/td][td]
7​
[/td][td]
8​
[/td][td]
9​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.
 
Upvote 0
Perfect, perfect, perfect, thank you Marcelo, that's exactly what I needed, I really appreciate your help, Thank you.
Serge.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
91
helpcharity
H
E
  • Solved
Excel help with formula please.
Replies
9
Views
342
felixstraube
F
D
  • Question Question
Xlookup or similar
Replies
6
Views
122
Peter_SSs
Peter_SSs
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
J
  • Question Question
Create a single list of records
Replies
3
Views
213
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

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