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 obviously missing -001 and -002 so I'll know the result is correct when at least those 2 are there.
Thank You, Walt
| -028-Floral Shop Wooden Post-028 | 37 |
| -014-Sub Shop/Pizza Area-014 | 35 |
| -021-Bakery Cold Case Area-021 | 34 |
| -026-Produce Cold Case Salad Pkg Area-026 | 34 |
| -025-Produce Cut Fruit/Veggie Area-025 | 33 |
| -023-Produce Cut Veggies Case-023 | 33 |
| -027-Produce Cold Case Organic Case Area-027 | 33 |
| -024-Floral Portable Cutting Machine-024 | 32 |
| -015-Marche/Soup Area-015 | 31 |
| -010-Indoor Seating Area #2-010 | 29 |
It happens to the best of us! If you can share more details about the data you're working with and the specific formulas you need, I'd be happy to help. Are you looking for Excel, Google Sheets, or another tool?
It happens to the best of us! If you can share more details about the data you're working with and the specific formulas you need, I'd be happy to help. Are you looking for Excel, Google Sheets, or another tool?
Great Solution for 1st scenario. NOW ONTO the 2nd scenario. Column A input....
-003-Outside Entrance Front Right Wall-003 -004-Left Vestibule Front -004 -005-Right Vestibule Front-005 -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 -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 -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 -026-Produce Cold Case Salad Pkg Area-026 -028-Floral Shop Wooden Post-028 -029-Deli/OWC Area-029 -030-FE Mens Restroom-030 -031-FE Womens Restroom-031 -032-Customer Service Desk-032 -033-Register #1-033 -034-Register #18-034 -035-Meals-To-Go Desk (catering)-035 -036-Aisle 2B Back-036 -037-Aisle 5A Front-037 -038-Aisle 6B Back-038 -039-Aisle 9A/9B Middle-039 -040-Aisle 10A Front-040 -041-Aisle 13B Steel Pole-041 -042-Aisle 14A Front-042 -043-Aisle 16B Back-043 -044-Aisle 17A Front-044 -045-Aisle 20/21 Paper Towels-045 -046-Aisle 22A Front-046 -047-Aisle 19A Front-047 -048-Dairy Milk/Egg Area-048 -049-Aisle 19B Water Area-049 -050-Dairy Yogurt Area-050 -051-Dairy Packaged Meat Area-051 Out of these 51, I have 13 in this group that are assigned to a certain route. I will need to pull them out and place in another tab (SO THATS MY QUESTION SCENARIO), also will then need to see if they missed any of those 13( i will use the previous formula that was just supplied).
-012-Café Mens Restroom-012 -013-Café Womens Restroom-013 -015-Marche/Soup Area-015 -016-Sushi/Rotisserie Area-016 -020-Donut/Bagel Area Black Pole-020 -021-Bakery Cold Case Area-021 -022-Produce Berry Area-022 -023-Produce Cut Veggies Case-023 -029-Deli/OWC Area-029 -030-FE Mens Restroom-030 -031-FE Womens Restroom-031 -032-Customer Service Desk-032 -033-Register #1-033 -034-Register #18-034 -035-Meals-To-Go Desk (catering)-035 Sorry, I didn't catch entire logic, could you please clarify a bit.
- do you have a list of all 51 locations somewhere?
- How Excel know which locations (13 in your sample) are assigned on this or that route?
- Could one location be assigned on more than one route?
- Is first list of locations contains non-assigned one?
- If so we update this list each time when new route is defined, correct?
- Do you have separate sheet for each new route, or one sheet into which all routes are collected?
Or how it works in general?
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
This is not a pristine solution, but assuming this is a one-off need, it works. And I'm also assuming, though you don't say it, that the 51 possible locations are numbered 001 through 051. I did it--I said it's not the most elegant or pristine solution--by simply changing the ones you DO have into numbers, and then creating a list of numbers 1 through 51 and using MATCH to identify the ones that do exist and, by their omission therefore, the ones that are missing.
A much cleaner solution could be devised, but as noted, I'm just giving you a quick and dirty,
