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
10,P10)>COUNTIF(P$1
$7,P10),AND(ISTEXT(P10),ISERROR(MATCH(P10,P$1
$7,0))))),"Create
new",IF(AND(COUNTIF(P$1
$7,P10)<COUNTIF(P$10
10,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
$7)),"Create
new",IF(AND(COUNTIF(P$1
$7,P10)<COUNTIF(P$10
10,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
$17))," Creat
new",IF(ROW(P1)>COUNTA(P$10
$17),IF(AND(COUNTIF(P$1
7,P1)<=COUNTIF(P$10
$17,P1),ISNUMBER(MATCH(P1,P$10
$17,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1
7,P1)<=COUNTIF(P$10
$17,P7),ISNUMBER(MATCH(P1,P$10
$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
$25)),"Create
new",IF(ROW(P1)>COUNTA(P$17
$25),IF(AND(COUNTIF(P$1
1,P1)<=COUNTIF(P$17
$25,P1),ISNUMBER(MATCH(P1,P$17
$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1
1,P1)<=COUNTIF(P$17
$25,P1),ISNUMBER(MATCH(P1,P$17
$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
1,P1)<=COUNTIF(P$17
$25,P1),ISNUMBER(MATCH(P1,P$17
$25,0))),"Excess-Match","Excess"),IF(AND(COUNTIF(P$1
1,P1)<=COUNTIF(P$17
$25,P1),ISNUMBER(MATCH(P1,P$17
$25,0))),"Match","Mismatch"))
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"Luke M" wrote:
