| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 if's, with 4 different results
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
|
|||
|
|||
|
4 if's, with 4 different results
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
|
|||
|
|||
|
4 if's, with 4 different results
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
|
|||
|
|||
|
4 if's, with 4 different results
OUTSTANDING !!!!
Your solution worked perfectly. Much, much appreciated. And I was worried I wasn't explaining it correctly. Thanks again, Steve "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
|
|||
|
|||
|
4 if's, with 4 different results
OK, I came across another 'situation', and am curious if there's a solution
for this: Whereas the current positions are less than the proposed.(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 | |||
| Too many 'if's! | Excel Discussion (Misc queries) | |||
| IF's | Excel Worksheet Functions | |||
| How many 'if's'? | Excel Discussion (Misc queries) | |||
| to many 'IF's'?? | Excel Worksheet Functions | |||
| How many if's? | Excel Discussion (Misc queries) | |||
Linear Mode
