Attn: Luke - followup to the 4 IF's from Friday


S

Steve

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 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
 
Ad

Advertisements

L

Luke M

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)),"Create
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)<=COUNTIF(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 said:
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 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!*


:

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
 
S

Steve

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)<=COUNTIF(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 said:
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)),"Create
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)<=COUNTIF(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 said:
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 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!*


:

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!*


:

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
 
L

Luke M

Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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 said:
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)<=COUNTIF(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 said:
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)),"Create
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)<=COUNTIF(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 said:
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


:

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!*


:

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!*


:

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
 
S

Steve

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 said:
Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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 said:
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)<=COUNTIF(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 said:
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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
L

Luke M

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 said:
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 said:
Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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 said:
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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
Ad

Advertisements

S

Steve

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 said:
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 said:
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 said:
Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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!*


:

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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
L

Luke M

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:Q9,"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,"Change 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 said:
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 said:
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 said:
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

:

Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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!*


:

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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
S

Steve

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(Q463>0,"create,"")
and this one in the K column =IF(Q463>0,"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 said:
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:Q9,"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,"Change 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 said:
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 said:
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!*


:

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

:

Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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!*


:

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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
L

Luke M

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 said:
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(Q463>0,"create,"")
and this one in the K column =IF(Q463>0,"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 said:
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:Q9,"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,"Change 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 said:
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


:

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!*


:

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

:

Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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!*


:

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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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!*


:

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!*


:

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
 
Ad

Advertisements

L

Luke M

Glad to help. Thanks for the feedback.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Steve said:
Perfect. Glad something was finally easy.

Thanks again for everything,
Steve


Luke M said:
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 said:
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(Q463>0,"create,"")
and this one in the K column =IF(Q463>0,"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





:

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:Q9,"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,"Change 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!*


:

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


:

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!*


:

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

:

Input into Q10, copy down.
=IF(AND(ISTEXT(P10),ROW(P10)-9>COUNTA(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,"Change 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!*


:

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)<=COUNTIF(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
:

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)),"Create
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)<=COUNTIF(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!*


:

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


:

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" wrote:
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top