I've tried many function, but here's what I need:
I would like a cell G1, to return yes or no, if A1 contains all of cells B1:F1 in no specific order. Image:
masterexample.jpg
Looking forward to hear back!
I have attached a workbook if that helps.
[SOLUTION:] Ok so the solution isn't a formula exactly and it's not free. I just downloaded a free trail of ablebits or something and used the "Compare Tables" button. Worked like a charm.
Last edited by degromek; 09-24-2015 at 02:47 PM. Reason: SOLVED
Here!
=IF(ISNUMBER(MATCH("*"&C1&" "&D1&" "&E1&" "&F1&" "&G1&"*",$A$1:$A$12,0)),"Yes","No")
Drag Down as required
Cheers!
Deep Dave
Hi, welcome to the forum
Your comment...
...does that mean they could be in any sequence in the row?in no specific order.
If not, Dave's formula should work. If they can, then it wont always work
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I understood this to be - In no specific row number..in no specific order
If I understood you wrong, then FDibbins is right..
Thank you, I will be coming here often due to the work i need to do with little experience
The formula works for the example i gave.FDibbins is right
I tried to simplify my problem but even with this formula i can't get it to work for the actual file.
Let me place a small piece of the actual file i need work on. The original file has 7000+ rows of products.
This one is the first 20 with what should be only one "Yes" return.
masterexample2.jpg
Workbook File:
@ G8 SHOULD SHOW "YES" SINCE B8:F8 ALL MATCH A4.
Actually they don't.
A4 is 1 NEW 145 70 12 SUMMIT DS618 P145/70R12 R12 69T TIRE
B8:F8 is 145 70 12 SUMMIT DS615
But here's one way to do it from the first workbook. Array-entered in H1 and filled down.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
In the second workbook this array-entered in G1 should work filled down.Formula:Please Login or Register to view this content.
Dave
Hey Dave, that works but only for that one lookup. What if for example i need to:
-find all the data in B1:F1
-and return Yes or No
-if it exists in range A1:A20
Modifying the formula you gave me doesn't work if I search through a range "$A$1:$A$20" instead of "$A$4".
workbook3.jpg
CORRECTION: "SUMMIT DS615" was mispelled and corrected to match "SUMMIT DS618"
CORRECTION #2: B19:F19 should now also show yes since A15 has a match. (Just to add some variety)
Still haven't figured this out even with the smarties i work with
how about countif?
Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
With new info I got exactly what vlady came up with.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks