Forum Discussion
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...
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
)
Awesome, works as intended, column A was sorted to confirm the theory. 001, 002, 008 are the 3 missing locations. Here is screenshot of 25 of the 51 items.
| -003-Outside Entrance Front Right Wall-003 | -001 |
| -004-Left Vestibule Front -004 | -002 |
| -005-Right Vestibule Front-005 | -008 |
| -006-Middle Entrance to Store Column-006 | |
| -007-Café Entrance (including door)-007 | |
| -009-Indoor Seating Area #1-009 | |
| -010-Indoor Seating Area #2-010 | |
| -011-Café Register Area-011 | |
| -012-Café Mens Restroom-012 | |
| -013-Café Womens Restroom-013 | |
| -014-Sub Shop/Pizza Area-014 | |
| -015-Marche/Soup Area-015 | |
| -016-Sushi/Rotisserie Area-016 | |
| -017-Resturant Foods Area Salmon Case-017 | |
| -018-Service Meat/Seafood Area-018 | |
| -019-Meat Cold Case Area-019 | |
| -020-Donut/Bagel Area Black Pole-020 | |
| -021-Bakery Cold Case Area-021 | |
| -022-Produce Berry Area-022 | |
| -023-Produce Cut Veggies Case-023 | |
| -024-Floral Portable Cutting Machine-024 | |
| -025-Produce Cut Fruit/Veggie Area-025 |