Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
I apologize if you already saw this additional question, because it was
buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Hi Steve,
Glad I could help. In answer to your question, how do you want it to handle the situation if you had had more 'Sat/Sun' combinations than in Current, but still had a 'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is it a match? The "Create new" half of the problem: Since your formula is in the Q column, it can really only display results in the Q column. This formula will show "Create new" if you have an extra spot that could be filled. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))) Note that it doesn't add anything to column P. To make values appear in a cell that is already blank, would require the use of VBA, which is several steps beyond what we have been discussing. Hopefully the use of a flag will do some good. As for the first part again, no formula will be able to actually change the value of P. The best it could do is flag it somehow. Let me know what you decide. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: I apologize if you already saw this additional question, because it was buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Luke,
It's close, but I'm probably still missing something. Here's my layout: P col Q col 1 Sat/Sun Match 2 Sat/Sun Match 3 Sat/Sun Match 4 Sat/Sun Match 5 Sun/Mon Match 6 Thu/Fri Mismatch 7 Tue/Wed Match The matches or mismatches in the above Q are results of your original formula. And I need to compare those above results with the below P col days off. Proposed P Q 10 Sat/Sun 11 Sat/Sun 12 Sat/Sun 13 Sat/Sun 14 Sun/Mon 15 Sun/Mon Create from mismatch 16 Tue/Wed 17 Tue/Wed Create new In the lower Q column, I'd like 'Create from mismatch' in Q 15 and Create new in Q17. All other cells Q10:Q14 & Q16 should be blank. This will tell me that I have to change the original Thu/Fri in row 6, because it is a mismatch, to the P15 Sun/Mon ( create from mismatch) & because I currently have only 7 positions, but need 8, the missing needed position is the Tue/Wed in P 17 ( create new). Here is the formula I used that I can't get to completely work. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$10:P$17))," Creat new",IF(ROW(P1)COUNTA(P$10:P$17),IF(AND(COUNTIF(P $1:P7,P1)<=COUNTIF(P$10:P$17,P1),ISNUMBER(MATCH(P1 ,P$10:P$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P7,P1)<=COUNTI F(P$10:P$17,P7),ISNUMBER(MATCH(P1,P$10:P$17,0)))," Match","Mismatch"))) This formula did correctly produce Create New in Q17. Regarding your 1st paragraph question, If I had more Sat/Sun's in proposed than in current, to create the # of Sat/Suns needed, they'd either be created new, or Changed to from the mismatch. It would depend on the matching ot mismatching, or even how many the difference was. Same with the Thu/Fri, it may be a match if it was in both the current and proposed, or it would be overwritten as the Creat from mismatch. Thanks again, Steve "Luke M" wrote: Hi Steve, Glad I could help. In answer to your question, how do you want it to handle the situation if you had had more 'Sat/Sun' combinations than in Current, but still had a 'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is it a match? The "Create new" half of the problem: Since your formula is in the Q column, it can really only display results in the Q column. This formula will show "Create new" if you have an extra spot that could be filled. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))) Note that it doesn't add anything to column P. To make values appear in a cell that is already blank, would require the use of VBA, which is several steps beyond what we have been discussing. Hopefully the use of a flag will do some good. As for the first part again, no formula will be able to actually change the value of P. The best it could do is flag it somehow. Let me know what you decide. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: I apologize if you already saw this additional question, because it was buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9COUNTA(P$1:P$7)),"Create new",IF(AND(COUNTIF(P$1:P$7,P10)<COUNTIF(P$10:P10, P10),COUNTIF(Q$1:Q$7,"Mismatch")COUNTIF(Q$9:Q9,"C hange mismatch")),"Change mismatch","")) I tested this a few times with different combinations, and from what I think you want to happen, this works. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Luke, It's close, but I'm probably still missing something. Here's my layout: P col Q col 1 Sat/Sun Match 2 Sat/Sun Match 3 Sat/Sun Match 4 Sat/Sun Match 5 Sun/Mon Match 6 Thu/Fri Mismatch 7 Tue/Wed Match The matches or mismatches in the above Q are results of your original formula. And I need to compare those above results with the below P col days off. Proposed P Q 10 Sat/Sun 11 Sat/Sun 12 Sat/Sun 13 Sat/Sun 14 Sun/Mon 15 Sun/Mon Create from mismatch 16 Tue/Wed 17 Tue/Wed Create new In the lower Q column, I'd like 'Create from mismatch' in Q 15 and Create new in Q17. All other cells Q10:Q14 & Q16 should be blank. This will tell me that I have to change the original Thu/Fri in row 6, because it is a mismatch, to the P15 Sun/Mon ( create from mismatch) & because I currently have only 7 positions, but need 8, the missing needed position is the Tue/Wed in P 17 ( create new). Here is the formula I used that I can't get to completely work. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$10:P$17))," Creat new",IF(ROW(P1)COUNTA(P$10:P$17),IF(AND(COUNTIF(P $1:P7,P1)<=COUNTIF(P$10:P$17,P1),ISNUMBER(MATCH(P1 ,P$10:P$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P7,P1)<=COUNTI F(P$10:P$17,P7),ISNUMBER(MATCH(P1,P$10:P$17,0)))," Match","Mismatch"))) This formula did correctly produce Create New in Q17. Regarding your 1st paragraph question, If I had more Sat/Sun's in proposed than in current, to create the # of Sat/Suns needed, they'd either be created new, or Changed to from the mismatch. It would depend on the matching ot mismatching, or even how many the difference was. Same with the Thu/Fri, it may be a match if it was in both the current and proposed, or it would be overwritten as the Creat from mismatch. Thanks again, Steve "Luke M" wrote: Hi Steve, Glad I could help. In answer to your question, how do you want it to handle the situation if you had had more 'Sat/Sun' combinations than in Current, but still had a 'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is it a match? The "Create new" half of the problem: Since your formula is in the Q column, it can really only display results in the Q column. This formula will show "Create new" if you have an extra spot that could be filled. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))) Note that it doesn't add anything to column P. To make values appear in a cell that is already blank, would require the use of VBA, which is several steps beyond what we have been discussing. Hopefully the use of a flag will do some good. As for the first part again, no formula will be able to actually change the value of P. The best it could do is flag it somehow. Let me know what you decide. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: I apologize if you already saw this additional question, because it was buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
It works great on my test file, but I can't get it to work on my real file. A
few questions: The -9 just before he first , does that 9 represent one more than the # of proposed ? And the last countif, do the 9's in the Q$9:Q9 represent the row right before the first of the proposed ? And thanks for your patience. Steve "Luke M" wrote: Input into Q10, copy down. =IF(AND(ISTEXT(P10),ROW(P10)-9COUNTA(P$1:P$7)),"Create new",IF(AND(COUNTIF(P$1:P$7,P10)<COUNTIF(P$10:P10, P10),COUNTIF(Q$1:Q$7,"Mismatch")COUNTIF(Q$9:Q9,"C hange mismatch")),"Change mismatch","")) I tested this a few times with different combinations, and from what I think you want to happen, this works. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Luke, It's close, but I'm probably still missing something. Here's my layout: P col Q col 1 Sat/Sun Match 2 Sat/Sun Match 3 Sat/Sun Match 4 Sat/Sun Match 5 Sun/Mon Match 6 Thu/Fri Mismatch 7 Tue/Wed Match The matches or mismatches in the above Q are results of your original formula. And I need to compare those above results with the below P col days off. Proposed P Q 10 Sat/Sun 11 Sat/Sun 12 Sat/Sun 13 Sat/Sun 14 Sun/Mon 15 Sun/Mon Create from mismatch 16 Tue/Wed 17 Tue/Wed Create new In the lower Q column, I'd like 'Create from mismatch' in Q 15 and Create new in Q17. All other cells Q10:Q14 & Q16 should be blank. This will tell me that I have to change the original Thu/Fri in row 6, because it is a mismatch, to the P15 Sun/Mon ( create from mismatch) & because I currently have only 7 positions, but need 8, the missing needed position is the Tue/Wed in P 17 ( create new). Here is the formula I used that I can't get to completely work. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$10:P$17))," Creat new",IF(ROW(P1)COUNTA(P$10:P$17),IF(AND(COUNTIF(P $1:P7,P1)<=COUNTIF(P$10:P$17,P1),ISNUMBER(MATCH(P1 ,P$10:P$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P7,P1)<=COUNTI F(P$10:P$17,P7),ISNUMBER(MATCH(P1,P$10:P$17,0)))," Match","Mismatch"))) This formula did correctly produce Create New in Q17. Regarding your 1st paragraph question, If I had more Sat/Sun's in proposed than in current, to create the # of Sat/Suns needed, they'd either be created new, or Changed to from the mismatch. It would depend on the matching ot mismatching, or even how many the difference was. Same with the Thu/Fri, it may be a match if it was in both the current and proposed, or it would be overwritten as the Creat from mismatch. Thanks again, Steve "Luke M" wrote: Hi Steve, Glad I could help. In answer to your question, how do you want it to handle the situation if you had had more 'Sat/Sun' combinations than in Current, but still had a 'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is it a match? The "Create new" half of the problem: Since your formula is in the Q column, it can really only display results in the Q column. This formula will show "Create new" if you have an extra spot that could be filled. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))) Note that it doesn't add anything to column P. To make values appear in a cell that is already blank, would require the use of VBA, which is several steps beyond what we have been discussing. Hopefully the use of a flag will do some good. As for the first part again, no formula will be able to actually change the value of P. The best it could do is flag it somehow. Let me know what you decide. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: I apologize if you already saw this additional question, because it was buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
No, the first 9 is one less than the starting row of proposed. I needed a
starting value of 1, but since the data began on row 10, I subtracted 9. I.e., if your proposed begins on row 15, subtract 14. You are correct, the Q$9:Q9 refers to one row above the proposed. Hope that gets your file working! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: It works great on my test file, but I can't get it to work on my real file. A few questions: The -9 just before he first , does that 9 represent one more than the # of proposed ? And the last countif, do the 9's in the Q$9:Q9 represent the row right before the first of the proposed ? And thanks for your patience. Steve "Luke M" wrote: Input into Q10, copy down. =IF(AND(ISTEXT(P10),ROW(P10)-9COUNTA(P$1:P$7)),"Create new",IF(AND(COUNTIF(P$1:P$7,P10)<COUNTIF(P$10:P10, P10),COUNTIF(Q$1:Q$7,"Mismatch")COUNTIF(Q$9:Q9,"C hange mismatch")),"Change mismatch","")) I tested this a few times with different combinations, and from what I think you want to happen, this works. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Luke, It's close, but I'm probably still missing something. Here's my layout: P col Q col 1 Sat/Sun Match 2 Sat/Sun Match 3 Sat/Sun Match 4 Sat/Sun Match 5 Sun/Mon Match 6 Thu/Fri Mismatch 7 Tue/Wed Match The matches or mismatches in the above Q are results of your original formula. And I need to compare those above results with the below P col days off. Proposed P Q 10 Sat/Sun 11 Sat/Sun 12 Sat/Sun 13 Sat/Sun 14 Sun/Mon 15 Sun/Mon Create from mismatch 16 Tue/Wed 17 Tue/Wed Create new In the lower Q column, I'd like 'Create from mismatch' in Q 15 and Create new in Q17. All other cells Q10:Q14 & Q16 should be blank. This will tell me that I have to change the original Thu/Fri in row 6, because it is a mismatch, to the P15 Sun/Mon ( create from mismatch) & because I currently have only 7 positions, but need 8, the missing needed position is the Tue/Wed in P 17 ( create new). Here is the formula I used that I can't get to completely work. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$10:P$17))," Creat new",IF(ROW(P1)COUNTA(P$10:P$17),IF(AND(COUNTIF(P $1:P7,P1)<=COUNTIF(P$10:P$17,P1),ISNUMBER(MATCH(P1 ,P$10:P$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P7,P1)<=COUNTI F(P$10:P$17,P7),ISNUMBER(MATCH(P1,P$10:P$17,0)))," Match","Mismatch"))) This formula did correctly produce Create New in Q17. Regarding your 1st paragraph question, If I had more Sat/Sun's in proposed than in current, to create the # of Sat/Suns needed, they'd either be created new, or Changed to from the mismatch. It would depend on the matching ot mismatching, or even how many the difference was. Same with the Thu/Fri, it may be a match if it was in both the current and proposed, or it would be overwritten as the Creat from mismatch. Thanks again, Steve "Luke M" wrote: Hi Steve, Glad I could help. In answer to your question, how do you want it to handle the situation if you had had more 'Sat/Sun' combinations than in Current, but still had a 'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is it a match? The "Create new" half of the problem: Since your formula is in the Q column, it can really only display results in the Q column. This formula will show "Create new" if you have an extra spot that could be filled. =IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))) Note that it doesn't add anything to column P. To make values appear in a cell that is already blank, would require the use of VBA, which is several steps beyond what we have been discussing. Hopefully the use of a flag will do some good. As for the first part again, no formula will be able to actually change the value of P. The best it could do is flag it somehow. Let me know what you decide. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: I apologize if you already saw this additional question, because it was buried in the thread after my initial response, but your original solution was so good, I was hoping to get another solution to this situation. OK, I came across another 'situation', and am curious if there's a solution for this: Whereas the current positions (7) are less than the proposed (8).(see below). Is there a formula that could be utilized next to the proposed positions that are needed to create. Meaning to change the Thu/Fri mismatch to a Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs. the 7 current. Thanks agian, Steve Current Sat/Sun Match Sat/Sun Match Sat/Sun Match Sat/Sun Match Sun/Mon Match Thu/Fri Mismatch Tue/Wed Match Proposed Sat/Sun Sat/Sun Sat/Sun Sat/Sun Sun/Mon Sun/Mon Change mismatch to this Tue/Wed Tue/Wed Create new "Luke M" wrote: Sorry, you did say your data was in P & Q. Input this into Q1 and fill down. =IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assumes your first set of data is in A1:A14, and proposed data is in A17:A25. =IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Not a very good subject wording, but.... I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun thru Tue/Wed]. - Current. I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed I want formulas in the Q col to indicate 4 different texts. Being that the top group is in seniority order, I want the upper group Q formulas to look at the lower days off, then starting at the top of the upper group, if it found a matching day off, return "match". In this first case, the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc. I also need anything below the count of 9- in current- to result in "excess' if they don't match the proposed need, and "excess match" if they do match the proposed need. The upper Q column shows how all the results should be. I hope I explained the ok. Current P Q Sat/Sun Match Sat/Sun Mismatch Sun/Mon Match Sun/Mon Match Sun/Mon Mismatch Thu/Fri Match Thu/Fri Match Thu/Fri Match Tue/Wed Match Tue/Wed Excess-Match Tue/Wed Excess-Match Tue/Wed Excess Tue/Wed Excess Tue/Wed Excess Proposed Sat/Sun Sun/Mon Sun/Mon Thu/Fri Thu/Fri Thu/Fri Tue/Wed Tue/Wed Tue/Wed Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a row - followup question | Excel Discussion (Misc queries) | |||
Followup on Format a Ratio | Excel Discussion (Misc queries) | |||
Followup: Folder listing | Excel Discussion (Misc queries) | |||
countif followup | Excel Worksheet Functions | |||
Bob Phillips followup question on text macro | Excel Discussion (Misc queries) |