Brentastic Posted March 30, 2010 Share Posted March 30, 2010 I know there are 'work arounds' for this like adding another column equation, but this is more of a challenge than a need. A challenge among co-workers. I'm trying to create a formula that identifies duplicates with 2 criteria - then if any of the singular items within that group meets the criteria, I want to flag all singular items in that group. For instance, let's say you have a list of football players and their jersey number and you want to find all duplicate last names who also have the jersey number 80 - this part I have figured out, it's: =IF(AND(COUNTIF($F$2:$F$424,F2)>1,J2=80),"Yes","No") This would flag the duplicates who have the same last name and also wear # 80 with a 'Yes'. So, let's say for this example, we returned a group of 20 players with the last name Smith but only 5 of those 20 Smiths wear # 80. My formula above would flag those 5 Smiths who also wear #80 with a 'Yes'. But what I want to do is flag the entire Smith population (all 20) with 'Yes'. In other words, I want to isolate the entire group of Smiths instead of just the Smiths with #80. I want to do this in one formula/cell. Quote Link to comment Share on other sites More sharing options...
Big Country Posted March 30, 2010 Share Posted March 30, 2010 Couldn't you do this by removing the critera for the match to number? Basically, reset the formula to search just for duplicates in the last name field? Or am I missing the question? Quote Link to comment Share on other sites More sharing options...
Brentastic Posted March 30, 2010 Author Share Posted March 30, 2010 Couldn't you do this by removing the critera for the match to number? Basically, reset the formula to search just for duplicates in the last name field? Or am I missing the question? I think you're missing on the question (or maybe I am). I don't want to identify the 20 Smiths - I only want to identify them if more than one of them wears # 80. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted March 30, 2010 Share Posted March 30, 2010 Couldn't you do this by removing the critera for the match to number? Basically, reset the formula to search just for duplicates in the last name field? Or am I missing the question? That is what I was thinking? When I got to the end I was confused with what the number had to do with it? Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted March 30, 2010 Share Posted March 30, 2010 I think you're missing on the question (or maybe I am). I don't want to identify the 20 Smiths - I only want to identify them if more than one of them wears # 80. Could you count the number of Yes's in your first equation and if greater than 1 then put "Yes"? Quote Link to comment Share on other sites More sharing options...
geeteebee Posted March 30, 2010 Share Posted March 30, 2010 Can you just reverse the argument and only count the 80's if their last name is Smith? I think that would work. Quote Link to comment Share on other sites More sharing options...
Big Country Posted March 30, 2010 Share Posted March 30, 2010 =IF(AND(COUNTIF($F$2:$F$424,F2)>1,J2=80),"Yes","No") Threw that formula into a sheet and put some dummy data in. That formula will give you any duplicated name, not just Smith, that wears the number 80. For example, I have a list that is all Smith, with two Jones in there, one with an 80, one with an 83, and it accurately gives a Yes to all the Smiths with an 80 and also a Yes to the Jones that wears 80. I think your best bet might be to set aside cells for criteria defintion, ie a cell where you enter the name you want to look for and the number you want. Then, in lieu of the F2 after the comma in the countif statement, you set a reference to the name entry cell, and, instead of J2=80, you set it as a reference to the number input cell, ie J2=$A$2 (be sure to use the $ signs to fix the reference, or it will only work on your first formula). Would this accomplish your need? Quote Link to comment Share on other sites More sharing options...
T_bone65 Posted March 30, 2010 Share Posted March 30, 2010 That is what I was thinking? When I got to the end I was confused with what the number had to do with it? Just keep posting please Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted March 30, 2010 Share Posted March 30, 2010 Just keep posting please ???? Quote Link to comment Share on other sites More sharing options...
Gonkis Posted March 30, 2010 Share Posted March 30, 2010 Just keep posting please +DD Quote Link to comment Share on other sites More sharing options...
T_bone65 Posted March 30, 2010 Share Posted March 30, 2010 ???? Thank you Quote Link to comment Share on other sites More sharing options...
Big Country Posted March 30, 2010 Share Posted March 30, 2010 Try this, it works on my sample spreadsheet that I tossed together. Column A has labels for criteria entry Column B has actual criteria entry Column C has a list of names Column D has a list of numbers Column E has the formula Row 1 is column headers and data begins in Row 2. Formula in Cell E2 is =IF(AND(COUNTIF($C$2:$C$29,$B$2)>1,C2=$B$2,D2=$B$3),"Yes","No") This formula checks for more than one occurence of the last name in column C, then checks that the last name in column C matches the name entered in column B as the criteria, then checks that the number in column D matches the number entered in column B. If all arguments are true, then the field displays YES, if any of the three is false, it displays NO Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.