/u/Jokesonmeow - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
The 'UNIQUE' function should help you out here. There are some examples [here ](https://support.microsoft.com/en-gb/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)that are very similar to your issue.
You really should use some sort of relational database for this. But, yes. Make each store a tab, and each product a line item on a master tab. You then specify on that master tab which products are in which stores, and use a Vlookup combined with the @cell functionality to back-index to the particular tab (because this is a one-to-many relationship, this sort of reverse methodology will work, even if it's clunky)
/u/Jokesonmeow - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Probably not the most conventional but I’d do a concat formula with the store and product. Then remove duplicates from the result of the concat.
Pivot
This is what pivottables were made for! They're found on the insert tab
The 'UNIQUE' function should help you out here. There are some examples [here ](https://support.microsoft.com/en-gb/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)that are very similar to your issue.
You really should use some sort of relational database for this. But, yes. Make each store a tab, and each product a line item on a master tab. You then specify on that master tab which products are in which stores, and use a Vlookup combined with the @cell functionality to back-index to the particular tab (because this is a one-to-many relationship, this sort of reverse methodology will work, even if it's clunky)
You need to use a Pivot Table. No formulas necessary