Collect values from another sheet

PeggyLang

I have a worksheet with 5 columns that are multi-select dropdowns. The dropdown for each of the 5 columns is exactly the same; 400+ Store #'s.
I would like to collect in another sheet ALL of the selections from one column and compare those to the actual list of Store #'s. The object is to identify if a store # has been inadvertently left out.

I'm really not sure how to go about this. I thought JOIN, but that just gives me a string of #'s in one cell. I'm not sure how I would compare that to a list of Store #'s and determine if there were any missing.

Kelly P.

@PeggyLang You can add a checkbox column to the sheet that has all of your store numbers. In this checkbox column, you'd put a formula similar to what's below (mine only has the first two of the five parts…you'd need to finish that out). Finally, you could filter on that checkbox column to see only blanks which would be your missing stores.

=IF(COUNTIFS({OtherSheet_ODDMTHWK1TOC}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, IF(COUNTIFS({OtherSheet_NextColumn}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, 0), then continue with the other three columns)

Hope this helps!

Kelly P.

@PeggyLang You can add a checkbox column to the sheet that has all of your store numbers. In this checkbox column, you'd put a formula similar to what's below (mine only has the first two of the five parts…you'd need to finish that out). Finally, you could filter on that checkbox column to see only blanks which would be your missing stores.

=IF(COUNTIFS({OtherSheet_ODDMTHWK1TOC}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, IF(COUNTIFS({OtherSheet_NextColumn}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, 0), then continue with the other three columns)

Hope this helps!

PeggyLang

@Kelly P.
WOW!
I was SO overthinking this solution. You hit the nail right on the head. Easy peasy, thanks for showing me the trees in the forest!!!! Worked like a charm.

Kelly P.

@PeggyLang Happy to help!! 😊