Need Excel formula

[Bird222]

I need a formula that will tell me if every item in one column range is somewhere in a second column. The following formula tells me if one item (A100 in this case) is in a column. =SUMPRODUCT(--($A100=$A$10:$A99))>0 I tried to do something like =SUMPRODUCT(--($A150:$A170=$A$10:$A99))>0 but that doesn't work.

 

[deadlyapp]

You want the sumifs function

 

[s0me0nesmind1]

VLOOKUP solves everything amirite?

 

[purbeast0]

Does this have to be done in excel? Or is this something you just need to get the data for?

This could be done in javascript with node in like 5 minutes.

 

[Bird222]

Does this have to be done in excel? Or is this something you just need to get the data for?

This could be done in javascript with node in like 5 minutes.

Yes Excel.

 

[snoopy7548]

=ISEVERYONEOFTHESEITEMSIN(ACOLUMN)ISINTHEOTHERCOLUMN(BCOLUMN)

 

[KillerCharlie]

That is such unreadable garbage... glad I gave up Excel years ago, even for simple calculations.

Using python or some other scripting language sounds like overkill but it's not... You'll be able to tell what a script is doing just by glancing at it, unlike those ridiculous Excel formulas.

 

[sdifox]

Column of formula sure. One formulae, not so much.

assuming A1:A10 value you seek and B1:B10 the pool you are looking from

in C1 put formulae =countif(B1:B10,A1)
copy down column C
sum column C

 

[Bird222]

Column of formula sure. One formulae, not so much.

assuming A1:A10 value you seek and B1:B10 the pool you are looking from

in C1 put formulae =countif(B1:B10,A1)
copy down column C
sum column C

I basically have this already. I am looking for one formula.

 

[PowerEngineer]

That's a tough ask! Is this really a "need" or just more of a "want"? In my own admittedly limited experience, trying to cram too much into a formula for a single cell (rather than spreading it out over an extra row or column) has always led to maintenance headaches. But, good luck!

 

[Scarpozzi]

I basically have this already. I am looking for one formula.

I have to agree that CountIF is the function to use. A2:A10 as the search range in this example and you'll have to drag the formula to change the value you're checking against so B2 will change to B3, B4, B5, etc and you'll get your line by line answer True/False on whether or not there's a match.

=COUNTIF(A2:A10,B2)>0

If you wanted to do this another way, you could take 2 separate CSV files and use Powershell. In that example, you'd use the compare commandlet. Create the two CSV files with a single column (or multiples) let's say the header for your values is "VALUE"

$file1 = import-csv "C:\file1.csv"
$file2 = import-csv "C:\file2.csv"
Compare $file1 $file2 -property "VALUE" -includeequal -passthru|export-csv "C:\output.csv" -notypeinfo
The indicator in the last column will tell you if the value exists only in <= File 1, => File 2, or == Both.

IF you only want to see the ones that match, you can just filter the output in the pipeline before saving:
Compare $file1 $file2 -property "VALUE" -includeequal -passthru|?{$_.SideIndicator -eq '=='}|export-csv "C:\output.csv" -notypeinfo

I gave up on doing stuff in Excel because powershell is much faster when you're working with thousands of records between multiple CSV files. It takes too damn long to drag forumulas.

 

[BarkingGhostar]

Why can't he just use a vlookup function?

 

[Scarpozzi]

Why can't he just use a vlookup function?

Requires the same as the CountIF….you have to copy the formula to all the rows. That's why I like the scripting approach.

 

[BarkingGhostar]

Its a simple double-click of the first cell. Copies down the entire length of the column.

 

[Bird222]

Thanks to those that tried. I found a solution. Column D is the column I want to check for the items in column A.

=sumproduct(--(countif(d2:d6,a2:a4)=0))=0

 

[PowerEngineer]

Thanks to those that tried. I found a solution. Column D is the column I want to check for the items in column A.

=sumproduct(--(countif(d2:d6,a2:a4)=0))=0

Wow! 😲

I didn't realize that you can use arrays in a COUNTIF criteria and get an array of results. I wonder if this is a newer feature; certainly works in Office 365. The "=0" is an implied IF statement? And the first minus converts the array of logicals into integers and the second sign corrects the sign (back) so that the result is the number of unmatched items. Learn something every day! I think you might be able to use SUM instead of SUMPRODUCT.

Thanks!

 

[Bird222]

Wow! 😲

I didn't realize that you can use arrays in a COUNTIF criteria and get an array of results. I wonder if this is a newer feature; certainly works in Office 365. The "=0" is an implied IF statement? And the first minus converts the array of logicals into integers and the second sign corrects the sign (back) so that the result is the number of unmatched items. Learn something every day! I think you might be able to use SUM instead of SUMPRODUCT.

Thanks!

Are you asking a question about the first "=0"? If so, SUMPRODUCT is taking the COUNTIF results and checking if each one is equal to 0 (this 'flips' the results from the COUNTIF function). That gives an array of TRUE and FALSE which are then converted to 1's and 0's by the math operations (--). Nope can't use SUM. The COUNTIF fails on the array (you get #VALUE) if you use SUM. Must use SUMPRODUCT.

 

[PowerEngineer]

Are you asking a question about the first "=0"? If so, SUMPRODUCT is taking the COUNTIF results and checking if each one is equal to 0 (this 'flips' the results from the COUNTIF function). That gives an array of TRUE and FALSE which are then converted to 1's and 0's by the math operations (--). Nope can't use SUM. The COUNTIF fails on the array (you get #VALUE) if you use SUM. Must use SUMPRODUCT.

Actually, SUM does work for me. I'm guessing the difference we're seeing is due to different versions of Office. Thanks again for sharing your solution. Proves to me once again just how superficial my appreciation of Excel's potential really is!

 

[Bird222]

Actually, SUM does work for me. I'm guessing the difference we're seeing is due to different versions of Office. Thanks again for sharing your solution. Proves to me once again just how superficial my appreciation of Excel's potential really is!

Out of curiosity, what version of Excel are you running?

 

[PowerEngineer]

Out of curiosity, what version of Excel are you running?

I'm running a business subscription for Office 365, so presumably the latest and greatest. I noticed that they recently added enhancements for handling dynamic arrays in cell formulas that may not be available in other versions.

 

[Bird222]

I'm running a business subscription for Office 365, so presumably the latest and greatest. I noticed that they recently added enhancements for handling dynamic arrays in cell formulas that may not be available in other versions.

Ok. The formula actually looks like it still works with SUM (i.e. you get a result) but when you step through it with 'evaluate formula' you see that the COUNTIF function doesn't return the correct results. I noticed the SUM version was wrong when I got 'FALSE' for a result when the SUMPRODUCT version returned 'TRUE'.

 

[PowerEngineer]

Hmmm... I am puzzled, as both versions seem to give me identical (correct) results for the test lists I am playing with. Obviously not a big deal. You only need one of them to work. 👍