NARAYANK991
Excel Ninja
Hi ,
No problem. Give me some time.
Narayan
No problem. Give me some time.
Narayan
Formula that checks for duplicates, and then indicates first occurrence
- Thread starter Grumpy88
- Start date
NARAYANK991
Excel Ninja
Hi ,
I have thought over what you explained in your last post , and I am slightly confused ; suppose the data is as follows :
[pre]
[/pre]
What result do you want against each combination ?
If you are going to repeat the result for every occurrence of a particular combination , then essentially the unique test is no longer necessary , in which case the COUNTIFS can be removed.
Can you clarify ?
Narayan
I have thought over what you explained in your last post , and I am slightly confused ; suppose the data is as follows :
[pre]
Code:
Criterion One Name 1
Criterion One Name 1
Criterion 5 Name 1
Criterion 2 Name 1
Criterion 2 Name 1
Criterion One Name 1
Criterion 4 Name 1
Criterion One Name 1
Criterion 3 Name 1What result do you want against each combination ?
If you are going to repeat the result for every occurrence of a particular combination , then essentially the unique test is no longer necessary , in which case the COUNTIFS can be removed.
Can you clarify ?
Narayan
Grumpy88
Member
Hi.
Sorry, I see I got a bit confused in my column naming. There are only three Column F criteria, plus the names in Column H plus the additional new criterion in Column C (not Column F as stated in my last post).
So essentially, what the formula should do is to output "text 1" in Column I for every row where Column F has criterion 1, and the criterion in Column C and the name in Column H correspond. There could be several such rows.
However, as soon as the data in either Column C or Column H changes, the formula should not produce any output besides a dash. In fact, it should never produce anything other than a dash for that name again, unless the criterion in Column F changes to 2 or 3.
In practice therefore, the formula must work the same as it does now, with the exception that the "text 1","text 2" or "text 3" output must now not just appear in the first matching row of Column I, but rather in each row of that Column F / Column H combination that share the same criterion in Column C as well.
Have I explained that better, or should I give you an example?
Sorry, I see I got a bit confused in my column naming. There are only three Column F criteria, plus the names in Column H plus the additional new criterion in Column C (not Column F as stated in my last post).
So essentially, what the formula should do is to output "text 1" in Column I for every row where Column F has criterion 1, and the criterion in Column C and the name in Column H correspond. There could be several such rows.
However, as soon as the data in either Column C or Column H changes, the formula should not produce any output besides a dash. In fact, it should never produce anything other than a dash for that name again, unless the criterion in Column F changes to 2 or 3.
In practice therefore, the formula must work the same as it does now, with the exception that the "text 1","text 2" or "text 3" output must now not just appear in the first matching row of Column I, but rather in each row of that Column F / Column H combination that share the same criterion in Column C as well.
Have I explained that better, or should I give you an example?
NARAYANK991
Excel Ninja
Hi ,
No , things are clearer now ; you want that an additional column ( C ) also be included in the formula to derive the output.
However , I am not clear on what the output should be ; suppose we have Name 1 , and various values in column F , some of which are valid , some of which are invalid , some of which are unique and some which are repetitions. In the earlier formula , the invalid ones would generate a "-" output ; the repetitions would generate blanks , and the unique ones would generate one of "yes 1" , "yes 2" or "yes 3".
Now , if column C is also to be included , column C can also have various values ; are you saying that when "Criterion 1" first appears against Name 1 , the corresponding value in column C is recorded , and the output is "text 1" ; thereafter , as long as the combination of Name 1 , Criterion 1 and the recorded value of column C repeat , the output will also repeat ; the moment the value in column C changes , then for the same combination of Name 1 and Criterion 1 , the output will be "-".
Have I understood you correctly ?
Narayan
No , things are clearer now ; you want that an additional column ( C ) also be included in the formula to derive the output.
However , I am not clear on what the output should be ; suppose we have Name 1 , and various values in column F , some of which are valid , some of which are invalid , some of which are unique and some which are repetitions. In the earlier formula , the invalid ones would generate a "-" output ; the repetitions would generate blanks , and the unique ones would generate one of "yes 1" , "yes 2" or "yes 3".
Now , if column C is also to be included , column C can also have various values ; are you saying that when "Criterion 1" first appears against Name 1 , the corresponding value in column C is recorded , and the output is "text 1" ; thereafter , as long as the combination of Name 1 , Criterion 1 and the recorded value of column C repeat , the output will also repeat ; the moment the value in column C changes , then for the same combination of Name 1 and Criterion 1 , the output will be "-".
Have I understood you correctly ?
Narayan
Grumpy88
Member
That's pretty much accurate, although the formula should not produce any blanks.
The data in Column F will always be valid, and can only be one of three different options: "Criterion 1", "Criterion 2" or Criterion 3". There are many, many different names in Column H, with many, many repetitions between them as well. The data in Column C is actually a seqeuential number, which can span several rows, but once finished is never repeated again.
Thus, in the first row where "Name 1" appears in Column H, "Criterion 1" appears in Column F and "No. 1" appears in Column C, for example, the formula should output "text 1". That same "text 1" output must also appear in every other row where "Name 1", "Criterion 1" and "No. 1" appear in their respective columns.
Thereafter, for every subsequent appearance of "Name 1" in Column H, the formula should output a dash, regardless of the Column C data, if Column F contains "Criterion 1".
As soon as Column F's data changes to "Criterion 2" (or "Criterion 3") for the first time, then for all "Name 1" rows that have the same entry in Column C (this sequential number may have increased to "25" or more by then, for example), the formula's output should then be "text 2" (or "text 3" for "Criterion 3" rows).
The same pattern should repeat for every other individual name in Column H. Thus, any one given name appearing in Column H can only ever have three different formula outputs ("text 1", "text 2" and/or "text 3") in the entire worksheet, but there will be numerous occurances of each "text X" output - in each row that shares the same Column C, F and H data. Currently, the formula's "text X" output only appears once for each name in the worksheet.
The data in Column F will always be valid, and can only be one of three different options: "Criterion 1", "Criterion 2" or Criterion 3". There are many, many different names in Column H, with many, many repetitions between them as well. The data in Column C is actually a seqeuential number, which can span several rows, but once finished is never repeated again.
Thus, in the first row where "Name 1" appears in Column H, "Criterion 1" appears in Column F and "No. 1" appears in Column C, for example, the formula should output "text 1". That same "text 1" output must also appear in every other row where "Name 1", "Criterion 1" and "No. 1" appear in their respective columns.
Thereafter, for every subsequent appearance of "Name 1" in Column H, the formula should output a dash, regardless of the Column C data, if Column F contains "Criterion 1".
As soon as Column F's data changes to "Criterion 2" (or "Criterion 3") for the first time, then for all "Name 1" rows that have the same entry in Column C (this sequential number may have increased to "25" or more by then, for example), the formula's output should then be "text 2" (or "text 3" for "Criterion 3" rows).
The same pattern should repeat for every other individual name in Column H. Thus, any one given name appearing in Column H can only ever have three different formula outputs ("text 1", "text 2" and/or "text 3") in the entire worksheet, but there will be numerous occurances of each "text X" output - in each row that shares the same Column C, F and H data. Currently, the formula's "text X" output only appears once for each name in the worksheet.
NARAYANK991
Excel Ninja
Hi ,
Things are almost clear now. It would be nice if you could upload a file with sample data for columns C , F and H , since otherwise , I will have to generate some data to test the formula. Will that be possible ?
Narayan
Things are almost clear now. It would be nice if you could upload a file with sample data for columns C , F and H , since otherwise , I will have to generate some data to test the formula. Will that be possible ?
Narayan
NARAYANK991
Excel Ninja
Hi ,
OK. Let me see what I can do , and I'll post the formula , but only tomorrow morning , since it's late at night here.
Narayan
OK. Let me see what I can do , and I'll post the formula , but only tomorrow morning , since it's late at night here.
Narayan
NARAYANK991
Excel Ninja
Hi ,
Thanks , you too.
Narayan
Thanks , you too.
Narayan
SirJB7
Excel Rōnin
Hi, Grumpy88!
Sorry for arriving late, but I tested NARAYANK991's formulas and as usual they work fine. Wouldn't be easier if you access to upload a sample file with all the possible cases and combinations, from a cyber or another location with internet connection? Just to aid people who's trying to help you.
I replicated your described model, applied NARAYANK991's formulas and everything is Ok. But I can't test column C criteria conditions, as you didn't even paste a group of values as example.
Regards!
Sorry for arriving late, but I tested NARAYANK991's formulas and as usual they work fine. Wouldn't be easier if you access to upload a sample file with all the possible cases and combinations, from a cyber or another location with internet connection? Just to aid people who's trying to help you.
I replicated your described model, applied NARAYANK991's formulas and everything is Ok. But I can't test column C criteria conditions, as you didn't even paste a group of values as example.
Regards!
Grumpy88
Member
Hi SirJB7.
Column C contains only a range of sequential numbers for identification purposes, i.e. "1", "2", "3", etc. Currently it has reached all the way up to "262" in the full workbook.
Does that help any? If not, hopefully I'll have my own PC back by tonight, and I can upload a cut-down version of the worksheet via dropbox.
Thanks for your involvement.
Column C contains only a range of sequential numbers for identification purposes, i.e. "1", "2", "3", etc. Currently it has reached all the way up to "262" in the full workbook.
Does that help any? If not, hopefully I'll have my own PC back by tonight, and I can upload a cut-down version of the worksheet via dropbox.
Thanks for your involvement.
Grumpy88
Member
Hi all.
May I be as bold as to resurrect this post again (more than two years later), because I'd like to modify NARAYANK991's excellent formula in post #11 to include another additional criterion and haven't been able to get it to work as required myself.
I've incorporated the formula into my attached spreadsheet (in column D), where it has served me well for the last two years, but I would now like to extend its usefulness further (see dropbox link https://www.dropbox.com/s/pau3wgi0ps8cfgc/Sample.xlsx?dl=0 for the spreadsheet).
To explain what I need: The three yellow columns (B, F and L) are the ones that the formula in the blue column (D) must address. At present what the formula does is to look for a name in column B, compare it with the competition name in column L (there are three different ones) and determine whether or not it is the first time that that name appears in combination with the competition indicated in column L. If so, the formula must return the "debut" wording together with the competition name in column D. In other words, in the first row that a given name appears in the Two-Day competition, column D reflects "Two-Day Debut" in that row. Any subsequent Two-Day appearances by that same column B name in the same competition only get a dash in column D thereafter. The same would apply to the first row in which that name appears for the other two competitions - "Limited Overs" and "AMA20/20".
The existing formula in column D works perfectly for this purpose. However, I would like it slightly adapted in as far as it now must also consider the match number in column F. It should thus continue doing the same thing, but with the added proviso that for every row containing the same name (column B), same match number (column F) and same competition (column L) combination, the wording "(competition name) Debut" should now appear. In other words, the "Debut" wording should no longer just be restricted to the first row in which a given name appears next to a given competition, but should now rather appear in every other row for that name if it has the same column F match number as the initial row.
By way of example, the name of player "Davidson, Richard E" first appears in the Two-Day competition in row 2, so cell D2 correctly indicates that row as his "Two-Day Debut". However, he appears again in row 24, which is still part of match number 1 and thus is another performance that still forms part of his Two-Day debut. Cell D24 should thus also indicate "Two-Day Debut", and no longer a dash, as the column F match number hasn't changed - even though row 24 is now no longer the first time that that player name/competition name combination now appears.
My need for the change is that I need to be able to combine the results of multiple performances in someone's debut match, and not just the first performance if there is more than one. Can anyone please make that change to the formula for me?
Thanks!
May I be as bold as to resurrect this post again (more than two years later), because I'd like to modify NARAYANK991's excellent formula in post #11 to include another additional criterion and haven't been able to get it to work as required myself.
I've incorporated the formula into my attached spreadsheet (in column D), where it has served me well for the last two years, but I would now like to extend its usefulness further (see dropbox link https://www.dropbox.com/s/pau3wgi0ps8cfgc/Sample.xlsx?dl=0 for the spreadsheet).
To explain what I need: The three yellow columns (B, F and L) are the ones that the formula in the blue column (D) must address. At present what the formula does is to look for a name in column B, compare it with the competition name in column L (there are three different ones) and determine whether or not it is the first time that that name appears in combination with the competition indicated in column L. If so, the formula must return the "debut" wording together with the competition name in column D. In other words, in the first row that a given name appears in the Two-Day competition, column D reflects "Two-Day Debut" in that row. Any subsequent Two-Day appearances by that same column B name in the same competition only get a dash in column D thereafter. The same would apply to the first row in which that name appears for the other two competitions - "Limited Overs" and "AMA20/20".
The existing formula in column D works perfectly for this purpose. However, I would like it slightly adapted in as far as it now must also consider the match number in column F. It should thus continue doing the same thing, but with the added proviso that for every row containing the same name (column B), same match number (column F) and same competition (column L) combination, the wording "(competition name) Debut" should now appear. In other words, the "Debut" wording should no longer just be restricted to the first row in which a given name appears next to a given competition, but should now rather appear in every other row for that name if it has the same column F match number as the initial row.
By way of example, the name of player "Davidson, Richard E" first appears in the Two-Day competition in row 2, so cell D2 correctly indicates that row as his "Two-Day Debut". However, he appears again in row 24, which is still part of match number 1 and thus is another performance that still forms part of his Two-Day debut. Cell D24 should thus also indicate "Two-Day Debut", and no longer a dash, as the column F match number hasn't changed - even though row 24 is now no longer the first time that that player name/competition name combination now appears.
My need for the change is that I need to be able to combine the results of multiple performances in someone's debut match, and not just the first performance if there is more than one. Can anyone please make that change to the formula for me?
Thanks!