Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Common value for like values

Posted on 2011-10-08
Medium Priority
260 Views
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
0
Question by:Excellearner
• 5
• 4
• 2
• +1

LVL 26

Expert Comment

ID: 36937178
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

LVL 50

Expert Comment

ID: 36937189
Hello Brian,

regards, barry

0

LVL 26

Expert Comment

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

LVL 50

Accepted Solution

barry houdini earned 1200 total points
ID: 36937243
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

LVL 26

Expert Comment

ID: 36937254
Yep!
0

Author Comment

ID: 36938281
Barry,

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

Thank you
0

LVL 50

Expert Comment

ID: 36938398
=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

LVL 10

Assisted Solution

broro183 earned 800 total points
ID: 36939828
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

LVL 26

Expert Comment

ID: 36939900
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

LVL 10

Expert Comment

ID: 36939959
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 Comment

ID: 36952200
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

LVL 26

Expert Comment

ID: 36952252
... 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

LVL 50

Expert Comment

ID: 36952451
....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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll