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
)I knew there was a more elegant solution 🙂 and there's no one better qualified than SergeiBaklan to create it!
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 Sergei, Awesome Thank you. Works as intended. I sorted column A to make sure what locations were missed and 001, 002, 008 are the correct ones. This is 25 out of the 51 locations of full spreadsheet.
-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
