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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Getting closer. The -9 explanation really helped. Thanks.
Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Hi Steve,
Sorry for delay in getting back to you. My setup: Current is in rows 1:7, proposed is starting in row 10. In Q10: =IF(AND(COUNTIF(Q$1:Q$7,"Mismatch")<=COUNTIF(Q$9:Q 9,"Change mismatch"),OR(COUNTIF(P$10:P10,P10)COUNTIF(P$1:P$ 7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1:P$7,0 ))))),"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","")) From what you've told me, I think this does what you want. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Getting closer. The -9 explanation really helped. Thanks. Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
No problem. Perfect solution. Thank you very, very much.
Sorry I took a round about way of getting exactly what I needed explained properly. Now, if your not too fed up with me, I'm now thinking of something else, I think very simple, to save me some more time. If the Q column result is either change mismatch or create new, I like in the J column 'create' and in the K column, the text in the P column, like sample below: J K Q create Sun/Mon change mismatch create Sat/Sun create new create Tue/Wed change mismatch create Wed/Thu change mismatch create Thu/Fri create new I did try this formula in the J column =IF(Q4630,"create,"") and this one in the K column =IF(Q4630,"P463,"") but, I think, because the formulas are in the Q column, it's assuming every cell is 0, and producing the create days off in every cell, and I want that only in the cells that have corresponding 'create new's' or 'create from mismatch', so I guess that 0 won't work in this situation. Anyway, thanks again for all you assistance and patience. Steve "Luke M" wrote: Hi Steve, Sorry for delay in getting back to you. My setup: Current is in rows 1:7, proposed is starting in row 10. In Q10: =IF(AND(COUNTIF(Q$1:Q$7,"Mismatch")<=COUNTIF(Q$9:Q 9,"Change mismatch"),OR(COUNTIF(P$10:P10,P10)COUNTIF(P$1:P$ 7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1:P$7,0 ))))),"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","")) From what you've told me, I think this does what you want. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Getting closer. The -9 explanation really helped. Thanks. Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Always glad to help. As you said, this part is easy.
In J10: =IF(LEN(Q10)1,"Create","") In Q10: =IF(LEN(Q10)1,P10,"") Of note, you could display this all in one cell if you want, such as: =IF(LEN(Q10)1,"Create "&P10,"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: No problem. Perfect solution. Thank you very, very much. Sorry I took a round about way of getting exactly what I needed explained properly. Now, if your not too fed up with me, I'm now thinking of something else, I think very simple, to save me some more time. If the Q column result is either change mismatch or create new, I like in the J column 'create' and in the K column, the text in the P column, like sample below: J K Q create Sun/Mon change mismatch create Sat/Sun create new create Tue/Wed change mismatch create Wed/Thu change mismatch create Thu/Fri create new I did try this formula in the J column =IF(Q4630,"create,"") and this one in the K column =IF(Q4630,"P463,"") but, I think, because the formulas are in the Q column, it's assuming every cell is 0, and producing the create days off in every cell, and I want that only in the cells that have corresponding 'create new's' or 'create from mismatch', so I guess that 0 won't work in this situation. Anyway, thanks again for all you assistance and patience. Steve "Luke M" wrote: Hi Steve, Sorry for delay in getting back to you. My setup: Current is in rows 1:7, proposed is starting in row 10. In Q10: =IF(AND(COUNTIF(Q$1:Q$7,"Mismatch")<=COUNTIF(Q$9:Q 9,"Change mismatch"),OR(COUNTIF(P$10:P10,P10)COUNTIF(P$1:P$ 7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1:P$7,0 ))))),"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","")) From what you've told me, I think this does what you want. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Getting closer. The -9 explanation really helped. Thanks. Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Perfect. Glad something was finally easy.
Thanks again for everything, Steve "Luke M" wrote: Always glad to help. As you said, this part is easy. In J10: =IF(LEN(Q10)1,"Create","") In Q10: =IF(LEN(Q10)1,P10,"") Of note, you could display this all in one cell if you want, such as: =IF(LEN(Q10)1,"Create "&P10,"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: No problem. Perfect solution. Thank you very, very much. Sorry I took a round about way of getting exactly what I needed explained properly. Now, if your not too fed up with me, I'm now thinking of something else, I think very simple, to save me some more time. If the Q column result is either change mismatch or create new, I like in the J column 'create' and in the K column, the text in the P column, like sample below: J K Q create Sun/Mon change mismatch create Sat/Sun create new create Tue/Wed change mismatch create Wed/Thu change mismatch create Thu/Fri create new I did try this formula in the J column =IF(Q4630,"create,"") and this one in the K column =IF(Q4630,"P463,"") but, I think, because the formulas are in the Q column, it's assuming every cell is 0, and producing the create days off in every cell, and I want that only in the cells that have corresponding 'create new's' or 'create from mismatch', so I guess that 0 won't work in this situation. Anyway, thanks again for all you assistance and patience. Steve "Luke M" wrote: Hi Steve, Sorry for delay in getting back to you. My setup: Current is in rows 1:7, proposed is starting in row 10. In Q10: =IF(AND(COUNTIF(Q$1:Q$7,"Mismatch")<=COUNTIF(Q$9:Q 9,"Change mismatch"),OR(COUNTIF(P$10:P10,P10)COUNTIF(P$1:P$ 7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1:P$7,0 ))))),"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","")) From what you've told me, I think this does what you want. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Getting closer. The -9 explanation really helped. Thanks. Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Luke - followup to the 4 IF's from Friday
Glad to help. Thanks for the feedback.
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Perfect. Glad something was finally easy. Thanks again for everything, Steve "Luke M" wrote: Always glad to help. As you said, this part is easy. In J10: =IF(LEN(Q10)1,"Create","") In Q10: =IF(LEN(Q10)1,P10,"") Of note, you could display this all in one cell if you want, such as: =IF(LEN(Q10)1,"Create "&P10,"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: No problem. Perfect solution. Thank you very, very much. Sorry I took a round about way of getting exactly what I needed explained properly. Now, if your not too fed up with me, I'm now thinking of something else, I think very simple, to save me some more time. If the Q column result is either change mismatch or create new, I like in the J column 'create' and in the K column, the text in the P column, like sample below: J K Q create Sun/Mon change mismatch create Sat/Sun create new create Tue/Wed change mismatch create Wed/Thu change mismatch create Thu/Fri create new I did try this formula in the J column =IF(Q4630,"create,"") and this one in the K column =IF(Q4630,"P463,"") but, I think, because the formulas are in the Q column, it's assuming every cell is 0, and producing the create days off in every cell, and I want that only in the cells that have corresponding 'create new's' or 'create from mismatch', so I guess that 0 won't work in this situation. Anyway, thanks again for all you assistance and patience. Steve "Luke M" wrote: Hi Steve, Sorry for delay in getting back to you. My setup: Current is in rows 1:7, proposed is starting in row 10. In Q10: =IF(AND(COUNTIF(Q$1:Q$7,"Mismatch")<=COUNTIF(Q$9:Q 9,"Change mismatch"),OR(COUNTIF(P$10:P10,P10)COUNTIF(P$1:P$ 7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1:P$7,0 ))))),"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","")) From what you've told me, I think this does what you want. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Steve" wrote: Getting closer. The -9 explanation really helped. Thanks. Ok, it's creating the correct # of positions ( have 3, need 9, so creating 6 new positions), but not the correct ones: Here's what this scenario is producing: P col Q col current Sat/Sun match Thu/Fri match Tue/Wed match Proposed Sat/Sun should see nothing-because match Sun/Mon should see create new-because need Mon/Tue should see create new-because need Tue/Wed create new should see nothing-because match Tue/Wed create new should see create new-because need Wed/Thu create new should see create new-because need Thu/Fri create new should see nothing - because match Thu/Fri create new should see create new-because need Fri/Sat create new should see create new-because need E.g. The immediatley above shows a need of two Tue/Wed, and is creating two Tue/Wed, however, the current already has one Tue/Wed, so one of the proposed Tue/Wed should show 'nothing' [because no change is needed] and the other Tue/Wed should show create new. Thanks again for all your patience. It's really appreciated. Steve "Luke M" wrote: 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: |
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) |