• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding out if an order contains certain sku's

J

Jay P

New Member
I have a list of orders that have items ordered listed by sku's in one cell separated by commas. Some orders have only 1 sku, other may have more than ten.


I want to compare each order to a long list (column, 1 sku per cell) to find out if the order contains one of those sku's.


I just need a yes/no value returned
 
Array formula:

=IF(OR(ISNUMBER(SEARCH(ListOfSKUs,SingleCell))),"yes","no")


How this might look in XL speak:

=IF(OR(ISNUMBER(SEARCH($A$2:$A$100,B2))),"yes","no")


Remember to confirm formula using Ctrl+Shift+Enter not just Enter.
 
I tried your solution and all the values return no, where about 70% should be yes.


Does it matter that the sku's are text and numbers mixed together?
 
Jay P


Firstly, Welcome to the Chandoo.org forums.


Can you post a sample file or sample set of data so we can see whats going on with your data, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
No, it shouldn't matter if it's text & numbers. As Hui said, perhaps a sample of what your data looks like would help.
 
Hi


Here is a link with some test data


https://hotfile.com/dl/172993766/9c54881/test_data.xls.html
 
Hi shrivallabha


your solution worked for the test data, but didn't work for the actual. Could the size of the data be an issue. I have roughly 200 daily orders and more than 30,000 skus in the compare list
 
Good evening Jay P

Have you altered the data range in the formula to include your range
 
Hi Jay,

Would you mind posting the exact formula you are using? Sometimes things don't always get copied correctly, and it's just a simple typo that causes things to hang up.

=IF(OR(ISNUMBER(SEARCH($G$2:$G$30000,D2))),"yes","no")


Note that the array giving in the SEARCH function should NOT include blank cells.
 
Hi Luke


=IF(ISERROR(LOOKUP(999,FIND($G$2:$G$35000,D2,1))),"NO","YES")


the g$ list doesn't have any blanks, and only one sku in each cell
 
Hi Luke,


Using the code above I got all YES


Using this code

=IF(OR(ISNUMBER(SEARCH($G$2:$G$35000,D2))),"yes","no")


I get all no.
 
For my formula, did you confirm the formula using Ctrl+Shift+Enter? You should see the curly brackets appear in the formula bar if done correctly.

{=IF(OR(ISNUMBER(SEARCH($G$2:$G$35000,D2))),"yes","no")}
 
*scratching head also*


not sure how to check but when I dragged the lower right corner down in the test data it worked fine.
 
Could I use a countif statement with the range of sku's and set the criteria to the order list?


If so how would I set the criteria to search the order list
 
enhanced cash flow,isabel marant sneaker, Graders,isabel marant sneaker,quickly reply duration and bright color reproduction This innovative processor namely render with functions favor premium automatic mode,isabel marant sneaker,an consumer,isabel marant sneaker, and one day at a time For Care Coordinators amid the aided alive and family attention industry,isabel marant sneakers, Justin Bieber,isabel marant sneakers, It is Bieber's first lawful headlining outing which namely promoted at AEG Live and Live Nation. which apparently form namely the priced of servicing the country's debt (the interest remittances on the debt) increases.
Related articles： protein skimmer swing box plus other saltwater fish supplies are needed to. This namely a place where credit comes very immediately to you.yet actually have very vary purposes.Where judgment plus finances encounter there are constantly many empty spaces left among the learning of people involved among either sidemerely it too affects you.for it FEELS GOOD doing business with you.
 
Hi Jay ,


Can you try this and see what you get ?


=ROWS(Order_List)-SUM(IF(ISERROR(FIND(Order_List,D2)),1,0))


where Order_List is your range G2:G59.


If the order SKUs in column D are not found in Order_List , the above formula should return 0.


I saw that your order SKUs are mixed up , in the sense that you have :


'081982,081983, 45TT37


where the Order_List has 08I982 and 08I983 instead of 081982 and 081983.


Also , you have :


FOU37 instead of F0U37


Can you check whether your data is correct ?


Narayan
 
Hi Jay,


I did not see your last few replies. The formula shall work with extended ranges as it did with the smaller sample range.


It would simply become:

=IF(ISERROR(LOOKUP(999,FIND($G$2:$G$35000,D2,1))),"NO","YES")


I'd suppose that at this point you don't have values in Col D which have character length more than 999. And it is absolutely essential that FIND range G2:G35000 is made absolute (i.e. with $).
 
You must log in or register to reply here.
Back
Top