# Excel BI Challenge Workout 193

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 193 —
(Solutions in any language are also welcome for Excel Challenges)

Listed are the strings where letters are in increasing order. Find the missing letters between first and last letters.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

My #powerquery solution:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
[
a = Text.ToList([String]),
b = List.Transform( a, each Character.ToNumber(_) ),
c = List.Min( b ),
d = List.Max( b ),
e = {c..d},
f = List.Transform(e, each Character.FromNumber(_) ),
g = List.RemoveMatchingItems( f, a),
h = Text.Combine( g, ", ")
][h])
in
``````

I thought my best and could come up with these many alternatives. Tell me which one you found best one. Each one is rising in character length. Which one would you believe will be performance efficient?

``````let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Source,
each [
ToList   = Text.ToList ( [String] ),
First   = List.First ( ToList ),
Last    = List.Last ( ToList ),
ActualList = { First .. Last },
Ans1    = Text.Combine ( List.Difference ( ActualList, ToList ), ", " ),
Ans2    = Text.Combine ( List.RemoveItems ( ActualList, ToList ), ", " ),
Ans3    = Text.Combine ( List.RemoveMatchingItems ( ActualList, ToList ), ", " )
][[Ans1], [Ans2], [Ans3]]
),
Return = Table.ExpandRecordColumn ( Answers, "Answer", { "Ans1", "Ans2", "Ans3" } )
in
Return
``````

## LinkedIn post by:John Jairo V.

Hi to all!

One option could be:

`=MAP(A2:A8,LAMBDA(x,LET(a,MID(x&" ",ROW(1:5),1),b,CODE(a),TEXTJOIN(", ",,TEXTSPLIT(CHAR(SEQUENCE(1+MAX(b)-@b,,@b)),a)))))`

Blessings!

`=MAP(A2:A8,LAMBDA(a,LET(d,CODE(a),s,CHAR(SEQUENCE(CODE(RIGHT(a))-d+1,,d)),TEXTJOIN(", ",,REPT(s,ISERR(FIND(s,a)))))))`

1#

``````=MAP(A2:A8,LAMBDA(a,
LET(
_tsplit,MID(a,SEQUENCE(LEN(a)),1),
_code,CODE(_tsplit),
_missingnum,DROP(_code,1)-DROP(_code,-1)-1,
IFERROR(ARRAYTOTEXT(TOCOL(CHAR(DROP(REDUCE("",SEQUENCE(ROWS(_missingnum)),LAMBDA(a,v,VSTACK(a,IF(INDEX(_missingnum,v,1),INDEX(DROP(_code,-1),v,1)+SEQUENCE(,INDEX(_missingnum,v,1)),"")))),1)),3,0)),""))))
``````

``````=ArrayFormula(
MAP(A2:A8,
LAMBDA(string,
LET(
alpha, TEXTJOIN("", 1, CHAR(SEQUENCE(26,1,97))),
rxSlice, REGEXREPLACE(string, "^(.).*(.)\$", "(\$1.*\$2)"),
slice, IFNA(REGEXEXTRACT(alpha, rxSlice), ),
REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(slice, "["&string&"]", ""),"(\w)","\$1, ") ,"(.*?),? ?\$","\$1")
))))
``````

`=LET(a,CHAR(ROW(97:122)),MAP(A2:A8,LAMBDA(b,TEXTJOIN(", ",,FILTER(a,(a>LEFT(b))*(a<RIGHT(b))*ISERR(FIND(a,b)),"")))))`

Or with ARRAYTOTEXT()

`=LET(a,CHAR(ROW(97:122)),MAP(A2:A8,LAMBDA(b,ARRAYTOTEXT(FILTER(a,(a>LEFT(b))*(a<RIGHT(b))*ISERR(FIND(a,b)),"")))))`

¡Hi!

`=IFERROR(MAP(A2:A8,LAMBDA(x,LET(c,CODE(MID(x,SEQUENCE(LEN(x)),1)),cs,SEQUENCE(MAX(c)-MIN(c)+1,,MIN(c)),ARRAYTOTEXT(TOCOL(CHAR(cs*ISERROR(XMATCH(cs,c,))),3))))),"")`

Or

`=IFERROR(MAP(A2:A8,LAMBDA(x,LET(c,CODE(MID(x,SEQUENCE(LEN(x)),1)),cs,SEQUENCE(MAX(c)-MIN(c)+1,,MIN(c)),ARRAYTOTEXT(FILTER(CHAR(cs),ISERROR(XMATCH(cs,c,)),""))))),"")`

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
let
a = Text.ToList([String]),
b = {a{0}..List.Last(a)},
c = List.RemoveItems(b,a)
in
Sol
``````

## LinkedIn post by: Zoran Milokanović

Missing Letters w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each let l = Text.ToList([String]) in
if List.Accumulate(List.Skip(List.Positions(l)), false, (s, c) => s or (l{c} <= l{c-1})) = true then "" else Text.Combine(List.Difference({List.Min(l)..List.Max(l)}, l), ", "))
in
Solution
``````

## LinkedIn post by:Hussain Ali Nasser

``````=BYROW(A2:A8,
LAMBDA(_string,
LET(_leftcode,UNICODE(LEFT(_string)),
_rightcode,UNICODE(RIGHT(_string)),
_returncorrseq,UNICHAR(SEQUENCE(_rightcode-_leftcode+1,,_leftcode,1)),
_lookup,TOCOL(MID(_string,SEQUENCE(LEN(_string)),1)),
_result,FILTER(_returncorrseq,NOT(ISNUMBER(XMATCH(_returncorrseq,_lookup))),""),
_check,IFNA(HSTACK(_leftcode,_rightcode,_returncorrseq,_lookup,_result),""),
TEXTJOIN(", ",,_result))))
``````

``````=MAP(A2:A8,LAMBDA(x,LET(_s,CODE(LEFT(x)),_e,CODE(RIGHT(x)),
_a,CHAR(SEQUENCE(_e -_s+1,,_s)),
_str,MID(x,SEQUENCE(LEN(x)),1),
_na,ISNA(XMATCH(_a,_str)),
TEXTJOIN(", ",1,FILTER(_a,_na,"")))))
``````

WHERE
_s - ASCII code of the first character of the string.
-e - ASCII code of the last character of the string.
_a - character from first character to the last character of the string
_na - boolean array indicating whether each character in _a is not present in the string.

``````=MAP(A2:A8,LAMBDA(x,LET(_m,MID(x,SEQUENCE(LEN(x)),1),_f,CODE(LEFT(x)),_c,SEQUENCE(CODE(RIGHT(x))-_f+1,,_f),IFERROR(TEXTJOIN(", ",,CHAR(FILTER(_c,ISNA(XMATCH(_c,CODE(_m)))))),""))))
``````

## LinkedIn Post by:Rafael González B.

Hi Everyone, Here’s my M Code:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
let

a = Text.ToList([String]),
l1 = List.First(a),
l2 = List.Last(a),
lf = {l1..l2},
lr = List.Difference(lf , a)
in
Text.Combine(lr , ","))

in
``````

``````=MAP(A2:A8,LAMBDA(a,LET(c,CODE(MID(a,SEQUENCE(LEN(a)),1)),d,MIN(c),s,SEQUENCE(MAX(c)-d+1,,d),TEXTJOIN(", ",,REPT(CHAR(s),ISNA(XMATCH(s,c)))))))
``````

## LinkedIn post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

`=MAP(A2:A8, LAMBDA(a, LET(_l, SEQUENCE(26,,97),_f, LEN(a),_s, CODE(MID(a, SEQUENCE(,_f), 1)), TEXTJOIN(", ",1, DROP(TAKE(REPT(CHAR(_l), MMULT(--(_s=_l), SEQUENCE(_f)^0)=0), MAX(_s)-96), MIN(_s)-97)))))`

``````=MAP(
A2:A8,
LAMBDA(a,
LET(
_tsplit, MID(a, SEQUENCE(LEN(a)), 1),
_code, CODE(_tsplit),
_left, LEFT(a),
_right, RIGHT(a),
_num, CODE(_left) + SEQUENCE(1 + CODE(_right) - CODE(_left), , 0),
_cri, ISNA(XMATCH(_num, _code)),
ARRAYTOTEXT(IFERROR(CHAR(FILTER(_num, _cri)), ""))
)
)
)
``````

``````=MAP(A2:A8, LAMBDA(X, LET(A, X,
B, CODE(MID(A,SEQUENCE(LEN(A)),1)),
C, MIN(B),
D, UNIQUE(VSTACK(B,SEQUENCE(MAX(B)-C+1,,C)),,1),
IFERROR(TEXTJOIN(", ",1,CHAR(D)),""))))
``````

``````=+LET(