Luke,

It's close, but I'm probably still missing something.

Here's my layout:

P col Q col
1 Sat/Sun Match
2 Sat/Sun Match
3 Sat/Sun Match
4 Sat/Sun Match
5 Sun/Mon Match
6 Thu/Fri Mismatch
7 Tue/Wed Match

The matches or mismatches in the above Q are results of your original formula.
And I need to compare those above results with the below P col days off.


Proposed
P Q
10 Sat/Sun
11 Sat/Sun
12 Sat/Sun
13 Sat/Sun
14 Sun/Mon
15 Sun/Mon Create from mismatch
16 Tue/Wed
17 Tue/Wed Create new

In the lower Q column, I'd like 'Create from mismatch' in Q 15 and Create
new in Q17. All other cells Q10:Q14 & Q16 should be blank.
This will tell me that I have to change the original Thu/Fri in row 6,
because it is a mismatch, to the P15 Sun/Mon ( create from mismatch) &
because I currently have only 7 positions, but need 8, the missing needed
position is the Tue/Wed in P 17 ( create new).

Here is the formula I used that I can't get to completely work.

=IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$10:P$17))," Creat
new",IF(ROW(P1)COUNTA(P$10:P$17),IF(AND(COUNTIF(P $1:P7,P1)<=COUNTIF(P$10:P$17,P1),ISNUMBER(MATCH(P1 ,P$10:P$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P7,P1)<=COUNTI F(P$10:P$17,P7),ISNUMBER(MATCH(P1,P$10:P$17,0)))," Match","Mismatch")))

This formula did correctly produce Create New in Q17.

Regarding your 1st paragraph question, If I had more Sat/Sun's in proposed
than in current, to create the # of Sat/Suns needed, they'd either be
created new, or Changed to from the mismatch. It would depend on the matching
ot mismatching, or even how many the difference was. Same with the Thu/Fri,
it may be a match if it was in both the current and proposed, or it would be
overwritten as the Creat from mismatch.

Thanks again,

Steve
"Luke M" wrote:

Hi Steve,
Glad I could help.

In answer to your question, how do you want it to handle the situation if
you had had more 'Sat/Sun' combinations than in Current, but still had a
'Thu/Fri' combination in Proposed? Should the Thu/Fri get overwritten, or is
it a match?

The "Create new" half of the problem:
Since your formula is in the Q column, it can really only display results in
the Q column. This formula will show "Create new" if you have an extra spot
that could be filled.
=IF(AND(ISBLANK(P1),ROW(P1)<=COUNTA(P$17:P$25)),"C reate
new",IF(ROW(P1)COUNTA(P$17:P$25),IF(AND(COUNTIF(P $1:P1,P1)<=COUNTIF(P$17:P$25,P1),ISNUMBER(MATCH(P1 ,P$17:P$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch")))

Note that it doesn't add anything to column P. To make values appear in a
cell that is already blank, would require the use of VBA, which is several
steps beyond what we have been discussing. Hopefully the use of a flag will
do some good.

As for the first part again, no formula will be able to actually change the
value of P. The best it could do is flag it somehow. Let me know what you
decide.
--
Best Regards,

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


"Steve" wrote:

I apologize if you already saw this additional question, because it was
buried in the thread after my initial response, but your original solution
was so good, I was hoping to get another solution to this situation.


OK, I came across another 'situation', and am curious if there's a solution
for this:
Whereas the current positions (7) are less than the proposed (8).(see below).
Is there a formula that could be utilized next to the proposed positions
that are needed to create. Meaning to change the Thu/Fri mismatch to a
Sun/Mon and create a new Tue/Wed because that's the 8th position needed vs.
the 7 current.

Thanks agian,

Steve


Current
Sat/Sun Match
Sat/Sun Match
Sat/Sun Match
Sat/Sun Match
Sun/Mon Match
Thu/Fri Mismatch
Tue/Wed Match

Proposed
Sat/Sun
Sat/Sun
Sat/Sun
Sat/Sun
Sun/Mon
Sun/Mon Change mismatch to this
Tue/Wed
Tue/Wed Create new


"Luke M" wrote:

Sorry, you did say your data was in P & Q. Input this into Q1 and fill down.

=IF(ROW(P1)9,IF(AND(COUNTIF(P$1:P1,P1)<=COUNTIF(P $17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(P$1:P1,P1)<=COUNTI F(P$17:P$25,P1),ISNUMBER(MATCH(P1,P$17:P$25,0)))," Match","Mismatch"))

--
Best Regards,

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


"Luke M" wrote:

Assumes your first set of data is in A1:A14, and proposed data is in A17:A25.

=IF(ROW(A1)9,IF(AND(COUNTIF(A$1:A1,A1)<=COUNTIF(A $17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0))),"Exc ess-Match","Excess"),IF(AND(COUNTIF(A$1:A1,A1)<=COUNTI F(A$17:A$25,A1),ISNUMBER(MATCH(A1,A$17:A$25,0)))," Match","Mismatch"))
--
Best Regards,

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


"Steve" wrote:

Not a very good subject wording, but....
I currently have 14 postitions (days off), sorted by seniority[top Sat/Sun
thru Tue/Wed]. - Current.
I only need 9 [the bottom Sat/Sun thru Tue/Wed] - Proposed
I want formulas in the Q col to indicate 4 different texts.
Being that the top group is in seniority order, I want the upper group Q
formulas to look at the lower days off, then starting at the top of the upper
group, if it found a matching day off, return "match". In this first case,
the Sat/Sun. Being that there is only one Sat/Sun in the below group, I want
the 2nd Sat/Sun in the upper group to return "mismatch", etc. etc.
I also need anything below the count of 9- in current- to result in "excess'
if they don't match the proposed need, and "excess match" if they do match
the proposed need.
The upper Q column shows how all the results should be.
I hope I explained the ok.

Current
P Q
Sat/Sun Match
Sat/Sun Mismatch
Sun/Mon Match
Sun/Mon Match
Sun/Mon Mismatch
Thu/Fri Match
Thu/Fri Match
Thu/Fri Match
Tue/Wed Match
Tue/Wed Excess-Match
Tue/Wed Excess-Match
Tue/Wed Excess
Tue/Wed Excess
Tue/Wed Excess


Proposed
Sat/Sun
Sun/Mon
Sun/Mon
Thu/Fri
Thu/Fri
Thu/Fri
Tue/Wed
Tue/Wed
Tue/Wed

Thanks,

Steve