# Common value for like values

Dear experts

I need a formulae in excel which will do the following

1. lookup for all like variables in column A with values in column B
2. Where the values in column B are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column C
3. Where the values in column E are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column F
4. Is it possible the folumae for both values, alpha numeric (column B) and text (column E) be the same for getting the 'true or False'

Kindly help.

Thank you,
common-values-for-like-variables.xls
###### Who is Participating?

Commented:
Hello Excellearner,

I was just about to give an answer very similar to Brian's, but then I saw his post and stopped.....but, thinking about it, perhaps this would be better as it's slightly shorter

=SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11<>B2))=0

regards, barry
0

Commented:
Hi. ExcelLearner.

Please see the attached. When copied to the highlighted cells, the formula in D2...
``````=COUNTIF(\$A\$2:\$A\$11,\$A2)-SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11=B2))=0
``````
... works for both tests.

Regards,
Brian.
common-values-for-like-variables.xls
0

Commented:
Hello Brian,

regards, barry

0

Commented:
Thanks, Barry. I have a good teacher. (Bow or blush - your choice!)
0

Commented:
Yep!
0

Author Commented:
Barry,

Fantastic, can you please explain the logic of the formula in very breif terms.

Thank you
0

Commented:
=SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11<>B2))=0

The SUMPRODUCT formula is simply counting the number of rows where the column A value is equal to the column A value on that row....but the column B value is not equal to the column B value on that specific row. Of course to get a TRUE there should be none of those rows because that would mean that the column B values could be different for the same column A value....so checking that the SUMPRODUCT is equal to zero effectively checks that there are none of those.

Brian's version is also a valid way subtracting the SUMPRODUCT form COUNTIF and checking the result is zero is the same as

=COUNTIF(\$A\$2:\$A\$11,\$A2)-SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11=B2))=0

is the equivalent of

=COUNTIF(\$A\$2:\$A\$11,\$A2)=SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11=B2))

If the count of column A values that match A2 (given by COUNTIF) is the same as the number of rows that match both A2 in column A and B2 in column B (given by SUMPRODUCT) then that means that all A2s in column A must also have a B2 in column B.

The two formulas should deliver the same results

regards, barry
0

Commented:
ExcelLearner,

If you are interested, the following link to a web page of Bob Phillips, provides a lot more detail & further examples of SumProduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Barry/Brian,
I've read Bob's page before but I glanced at it again before submitting this post & noticed the following statement in the Formula Efficiency section near the bottom of the linked page. It may not have an impact in this specific example, but I thought it interesting...
Even two COUNTIF /SUMIF functions are quicker than one SUMPRODUCT, so this formula
=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)

will be more efficient than this one,
=SUMPRODUCT((A1:A10>=10)*(A1:A10<=20))

by a factor of roughly 20%.

Rob
0

Commented:
Rob,

Thanks for the note. As I think you understand, the efficiency is not relevant in this case as SumProduct's extra functionality is required. Countif doesn't allows each Variable/Value pair to be individually checked and so wouldn't be able to differentiate, e.g.,  between the value "6" for Variable Y and Variable I.

Of course, we could have a "pure" CountIf solution, but that would have the overhead of a helper column to be added for each of the two tests (e.g. =A2&":"&B2") - so the first test would then be...
``````=COUNTIF(\$A\$2:\$A\$11,A2)-COUNTIF(\$H\$2:\$H\$11,H2)=0
``````
... assuming the helper column is in column H. That's not as efficient as Barry's proposal (or even mine for that matter!).

Regards,
Brian.
0

Commented:
hi Brian,

Thanks :-)
tbh, I hadn't worked the logic through completely so I wasn't sure if it would help in this case or not, but I do know with your further explanation.

Rob
0

Author Commented:
Barry,

Thank you for the comment and the detailed explanation.

In the below formula

SUMPRODUCT((\$A\$2:\$A\$11=\$A2)*(B\$2:B\$11<>B2))=0

=\$a2 is appearing but B2 is put and not \$b2.

Any particular reason why the \$ is not put for B2.

thank you
0

Commented:
... so when you copy the formula from D2 to G2 the "B" will change to "E" automatically.

["4. Is it possible the folumae for both values, alpha numeric (column B) and text (column E) be the same for getting the 'true or False'"]
0

Commented:
....that's right....

...and just on Rob's point - you could use COUNTIFS here - in Excel 2007 and later - that would be more efficient than SUMPRODUCT also, i.e.

=COUNTIFS(\$A\$2:\$A\$11,\$A2,B\$2:B\$11,"<>"&B2)=0

regards, barry

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.