Hi all,
How to find first missing number from column A where numbers which we have to consider are on the end of string. Result should be 3
Hi all,
How to find first missing number from column A where numbers which we have to consider are on the end of string. Result should be 3
Last edited by mtcas; 12-01-2020 at 03:02 AM.
Why should the result be 3? Where does that come from? My crystal ball has gone away for servicing, so you are going to need to explain what you mean by "numbers which we have to consider are on the end of string".
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you remove prefix "0612A" you have only numbers and then 3 is missing. I want to find this number.
Administrative Note:
Your thread title doesn't come near to saying what you are trying to do, so ...
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Pls try
B1 cell , Array Formula , Copy and drag down
HTML Code:
Ok, but when I extend range to 100 then formula not works
Administrative Note:
Sorry, but your posts do not comply with Rule #6 of our Forum RULES:
Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.
If you are unclear about the request or instruction, then send a private message to them asking for clarification.
All Participants:
Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.
Waiting for the title change requested. No further posts until this is done, please.
Last edited by AliGW; 12-01-2020 at 03:00 AM.
That's better - thank you. I will now reinstate the posts.
Increase the range $A$1:$A$6 to $A$1:$A$100
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hi mtcas,
=IFERROR(AGGREGATE(15,6,ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,"")))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$6,1,5,"")),ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,""))))))),ROWS(B$1:B1)),"")
As I wrote above, I extend range and not working
Hi Mtcas,
=IFERROR(AGGREGATE(15;6;ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$100;1;5;0));ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))));ROWS(B$1:B1));"")
regards
Christian
First of all, I want to know the total length of your characters is 6 digits ? 0612A1
if it is expanded to $A$1:$A$100 so many meaningless 1~9, my formula can be shorter
{=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$9),--RIGHT($A$1:$A$100),)=ROW($1:$9)),ROW($1:$9)),ROW(A1)),"")}
another option, perhaps:
=AGGREGATE(15;6;ROW($1:$9)/ISNA(MATCH("*"&ROW($1:$9);$A$1:$A$100;0));1)
the above would return 3, or 8 if 0612A3 were added.
In D2 ARRAY formula, then copy down for more missing numbers. If required change the range A1:A10
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
if the the digest are from 1 to 9 then shorter is:
=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$9);--(RIGHT($A$1:$A$100));0));ROW($1:$9));ROW(A1));"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks