4 if's, with 4 different results

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Match","Mismatch"))
 
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(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 said:
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(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 said:
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
Click to expand...
Click to expand...
 
OUTSTANDING !!!!

Your solution worked perfectly. Much, much appreciated.

And I was worried I wasn't explaining it correctly.

Thanks again,

Steve



Luke M said:
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(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 said:
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(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 said:
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))),"Excess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top