— 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.
Post answers in Comment.
(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)
Download Practice File - https://lnkd.in/dSYFb2nk
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI LinkedIn post:
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],
Answers = Table.AddColumn (
Source,
"Answer",
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
A #googlesheets solution with a whole load of REGEX:
=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")
))))
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
=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.
Hi Everyone, Here’s my M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn(Source, "Answer", each
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
Result[[Answer]]
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)))))
