LinkBack URL
About LinkBacks
Good day! I need to extract missing numbers in sequence from Column A to AX to Column AY to CV, or just simply identify or count any missing number. Here's the sample, please see attached file.) Thankss.
Good day! I need to extract missing numbers in sequence from Column A to AX to Column AY to CV, or just simply identify or count any missing number. Here's the sample, please see attached file.) Thankss.
One way:
=IFERROR(AGGREGATE(15,6,(COLUMN($A$2:$AV$2)+1001-1/(ISNA(MATCH(COLUMN($A$2:$AV$2)+1001-1,$A$2:$AV$2,0)))),COLUMNS($AY2:AY2)),"")
Change the bit in RED to reflect the FIRST number in your sequence.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
When I tried to move the to another column, the missing numbers changes, is there something I can adjust in the formula?
This is now a totally different Q. In your ONE ROW sample, the values were in ascending order left to right with no duplicates. Now it transpires that they can be in any order and can have duplicates.
However, after a bad tempered moment... it turns out not to be that difficult. I set up a Named Range (CTRL-F3 to view/edit... called Rng:
=IF(COLUMN(Sheet1!$K$1:$ZZ$1)-COLUMN(Sheet1!$K12)+MIN(Sheet1!$K5:$BH5)>MAX(Sheet1!$K5:$BH5),"",COLUMN(Sheet1!$K$1:$ZZ$1)-COLUMN(Sheet1!$K5)+MIN(Sheet1!$K5:$BH5))
I then used the formula:
=IFERROR(AGGREGATE(15,6,Rng/(ISNA(MATCH(Rng,$K5:$BH5,0))),COLUMNS($V5:V5)),"")
However, NEXT TIME make sure your sample meets our size guidelines (yellow banner, top) and is REALISTIC
Last edited by Glenn Kennedy; 12-21-2021 at 04:49 AM.
Try this. IN BI5 then copied to full region.
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.
Please try at BI5
=IFERROR(AGGREGATE(15,6,ROW($A$1:INDEX($A:$A,MAX($K5:$BH5)-MIN($K5:$BH5)))/ISNA(MATCH(MIN($K5:$BH5)+ROW($A$1:INDEX($A:$A,MAX($K5:$BH5)-MIN($K5:$BH5))),$K5:$BH5,)),COLUMNS($BI5:BI5))+MIN($K5:$BH5),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks