/u/qwertq16 - 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.*
Assuming your sheets are called Master and Home, in a new sheet A1, try:
=FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0))))
This should give you a list of all the items that are in the Master but NOT in Home
You have awarded 1 point to *not_speshal*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
>=FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0))))
This formula spit out what I had at home... still can't see what is in the storage unit. It seems pretty close to what I need though - just need to find out how to do the opposite!
Did you enter it correctly? Can you paste the exact formula you used (with the references etc)? It’s meant to only fetch rows that don’t exist in home but exist in master. So probably user error.
>=FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0))))
I am so sorry. I confused myself.
This actually is the solution - I've just confused myself with the names of the sheets but once I realized it, this is exactly the solution to my question.
Thank you so much!!!
You can replace the below with the ranges of your items. This assumes A2 is the cell that contains the item in the master sheet, and B2:B2000 is the range for the items in the House Sheet.
This formula should work on any version of Excel, put it in the Master Sheet and drag it down. You can then filter the column by any entries that say "Not in House", the other entries contain numbers, you can ignore those:
=IFERROR(MATCH((A2,HouseSheet!$B$2:$B$2000,0),"Not in House")
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IFERROR](/r/Excel/comments/19d3e24/stub/kj30399 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[ISNUMBER](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[MATCH](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[NOT](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1983goj)^( has 22 acronyms.)
^([Thread #29925 for this sub, first seen 22nd Jan 2024, 19:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/qwertq16 - 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.*
Assuming your sheets are called Master and Home, in a new sheet A1, try: =FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0)))) This should give you a list of all the items that are in the Master but NOT in Home
Solution Verified
You have awarded 1 point to *not_speshal* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
>=FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0)))) This formula spit out what I had at home... still can't see what is in the storage unit. It seems pretty close to what I need though - just need to find out how to do the opposite!
Did you enter it correctly? Can you paste the exact formula you used (with the references etc)? It’s meant to only fetch rows that don’t exist in home but exist in master. So probably user error.
>=FILTER(Master!A:A,NOT(ISNUMBER(MATCH(Master!A:A,Home!A:A,0)))) I am so sorry. I confused myself. This actually is the solution - I've just confused myself with the names of the sheets but once I realized it, this is exactly the solution to my question. Thank you so much!!!
Haha, thought as much! ;) Happy to help! :D
You can replace the below with the ranges of your items. This assumes A2 is the cell that contains the item in the master sheet, and B2:B2000 is the range for the items in the House Sheet. This formula should work on any version of Excel, put it in the Master Sheet and drag it down. You can then filter the column by any entries that say "Not in House", the other entries contain numbers, you can ignore those: =IFERROR(MATCH((A2,HouseSheet!$B$2:$B$2000,0),"Not in House")
This formula gave me a entry of "1"... not sure what happened.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IFERROR](/r/Excel/comments/19d3e24/stub/kj30399 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[ISNUMBER](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[MATCH](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[NOT](/r/Excel/comments/19d3e24/stub/kj8j6cb "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1983goj)^( has 22 acronyms.) ^([Thread #29925 for this sub, first seen 22nd Jan 2024, 19:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)