Excel: is there a subset function?

Question

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?

expletive_xom · Accepted 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

? · Answer

Excel Subset

Excel: is there a subset function?

Related Questions

Trending Questions