Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel: is there a subset function?
I have two rows of data, and I'm trying to program this rule:
If any of the values in the first row are not also in the second row, True.
If the second row contains everything in the first row, false.
Ex:
1st row: 500, 525, 550
2nd row: 500, 525
Result: true, because 550 is not in the second row.
Ex:
1st row: 5, 6, 8
2nd row: 5, 6, 8, 10
Result: false. Each element in the first row is also in the second.
Can this be done without element-by-element nested ifs,ands, and ors?
2 Answers
- expletive_xomLv 77 years agoFavorite Answer
no nested ifs, ands or ors? can you use a helper column of any sort?
you are using rows and not the usual columns...so for this test use A to E to get the logic down, then you can always expand the range later.
- A1 to E1 gets your subset
- A2 to E2 gets your larger set (looks like it could also be smaller)
- in A3 copy&paste this (it finds a match or no match is an error...the multiply by 1 just converts it to a 1 or a 0)
=ISERROR(MATCH(A1,$A$2:$E$2,0))*1
you should get 1 or 0
- you can copy A3 and paste in B3 to E3 (or just drag A3 across)
- in F5 (or any cell) copy&paste this formula
=IF(COUNTIF(A4:E4,0)=COUNTA(A1:E1), "false","true")
it counts the 0's and counts the # in the subset if it matches you get your false.
you can put the last formula in any cell, and hide row 3 if you want.
incidentally, since row 3 is binary, you could probably get this done all in 1 formula (either a CSE array or a SUMPRODUCT() formula) but you would still need an IF() because of your weird (reversed) true/false result.
give it a try and let me know how it worked out
