I am using two sheets to match inventories.
Sheet1 uses SKUs to search in Sheet2 to find the total stock quantity. It uses
=iferror(VLOOKUP("*"&$A1561&"*",'Sheet2'!$A$2:$B$2367,2,FALSE), "NA")
Everything works perfectly.
I want to do the same, but reverse on Sheet2. This is to find what SKUs are on Sheet2, but not listed one Sheet1. The problem is that the SKUs in Sheet2 are in a text string and have no uniformity among the other.
Sheet1
Capture1231.JPG
Sheet2
Capture21312.JPG
Welcome to the forum.
There is no 2020 version of Excel - you probably have MS365, which is what needs to be in your profile, please. Thanks.
See the first yellow banner at the top of the page telling you how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Here is the workbook
The SKUs you want to look up have 7 number digits. Is that ALWAYS the case?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try this, in sheet2, e2, copied down:
=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(Sheet1!$A$2:$A$7,A2)))))>0,"Yes","No")
No they have quite a few different formats.Originally Posted by Glenn Kennedy
No matter. See my proposed solution.
This worked!
Thank you very much!
Can you explain the "--" part of the formula?
Yes. It turns a true false array into a 1 0 array which SUMPRODUCT adds up.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks