Forum Discussion

Copper Contributor

Mar 19, 2025

Got in over my head, now I need formulas to get the right data.

A program creates an excel spreadsheet that has 2 columns, I need to know out of the 51 possible location points in column A, which ones are missing. And list those in say column D. This sample is ob...

excel

Formulas and Functions

office 365

SergeiBaklan

Diamond Contributor

Mar 20, 2025

Not sure I understood the logic, as variant

with

=LET(
   SourceRange,       $A$2:$A$11,
   PossiblePositions, "-" & TEXT( SEQUENCE(51), "000" ),
   AvailablePosition, LEFT(SourceRange,4),
   IsMissed,          ISNA( XMATCH(PossiblePositions, AvailablePosition) ),
   MissedPositions,   FILTER( PossiblePositions, IsMissed, "no missed" ),
   MissedPositions
)

mathetes

Gold Contributor

Mar 21, 2025

I knew there was a more elegant solution 🙂 and there's no one better qualified than SergeiBaklan to create it!