期間内での重複の確認

質問

いつもお世話なります。
WINDOWS7 EXCELL2010です。

下図を参考に

A列　ユーザ定義　mm/dd
B列 ユーザ定義　mm/dd
各列の数式は
E2 =A2&C2&D2
F2 =B2&C2&D2
G2 =IF(D2="","",IF(COUNTIF($E$2:E2,E2)>1,"重複",""))
H2 =IF(D2="","",IF(COUNTIF($F$2:F2,F2)>1,"重複",""))
I2 =IF(D2="","",IF(COUNTIF($E$2:F2,E2)>1,"重複",""))

条件付書式
範囲はA2:B10
=$I2=”重複”　紫
=$G2=”重複”　薄青
=$H2=”重複”　薄ピンク

という具合に「入所(A列)、退所(B列)」日での重複はセルの色で確認できますが
６の行は「入所、出所」日は対象にはなりません。
重複のチェックから漏れます。

この期間内の重複を防止したいのですが何かいい方法をご教示いただけませんでしょうか。
よろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ2です。
Ｆ2セルから下方にデータは合っています。Ｇ2セルら下方のデータは3，7，8，8，10となっていますね。
Ｈ2セルに入力する式は次のようになっていますか？もう一度確認してみてください。

=IF(OR(E1<>E2,E2=""),"",IF(F2>MAX(INDEX(F:F,MATCH(E2,E:E,0)):INDEX(G:G,ROW(A1))),"","重複"))

ところで新しく考えたのですが、Ｅ列の作業列はそのままにしてＦ，Ｇ列は使わずにＦ2セルには次の式を入力して下方にドラッグコピーすることで良いですね。

=IF(OR(E1<>E2,E2=""),"",IF(A2>MAX(INDEX(A:A,MATCH(E2,E:E,0)):INDEX(B:B,MATCH(E2,E:E,0)+COUNTIF(E$1:E1,E2)-1)),"","重複"))

お礼

下記を試させていただきました。
=IF(OR(E1<>E2,E2=""),"",IF(A2>MAX(INDEX(A:A,MATCH(E2,E:E,0)):INDEX(B:B,MATCH(E2,E:E,0)+COUNTIF(E$1:E1,E2)-1)),"","重複"))

私が要望していた通りで
ばっちりでした。

ご苦労かけて誠に済みませんでした。
ありがとうございました。

回答No.2

KURUMITO

作業列を作って対応します。
お示しのような作業列は無いものとして新たに作成します。
Ｅ２セルには次の式を入力して下方にドラッグコピーします。

=C2&D2

次にお示しの表では関係ありませんが例えば別の部屋番号などが有る場合で同じシートに入力している場合にはＥ列を重点に並び替えをして必ず同じグルーは同じ列に並ぶようにします。
部屋記号と部屋番号が同じものを1つのシートに入力している場合にはその必要もありません。
Ｆ2セルには次の式を入力してＧ2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($E2="","",RANK(A2,INDEX($A:$A,MATCH($E2,$E:$E,0)):INDEX($B:$B,MATCH($E2,$E:$E,0)+COUNTIF($E:$E,$E2)-1),1))

重複の判定はＨ列に表示させるとしてＨ2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(E1<>E2,E2=""),"",IF(F2>MAX(INDEX(F:F,MATCH(E2,E:E,0)):INDEX(G:G,ROW(A1))),"","重複"))

補足

早速ご指導を賜り有難うございます。
ご指導でテストさせていただきました。

参照図で言いますと何故か５行目に「重複」が表示されません。

参考に
F2～　１　４　４　６　２　とでています
H列は　４行目、６行目には　「重複」表示されてます

誠に申し訳ありませんが再度ご指導いただけませんんか。

回答No.1

noname#203218

入力規則で入所日は前回の入所日より古い日付でないと入力できないように設定する事を検討されては如何でしょう。

B2セルを選択
「データ」タブ→「データの入力規則」アイコンクリック
設定タブ　条件の設定
入力値の種類（A)→「日付」選択
データ（D)→「津比の値より大きい」選択
次の日から（S)→=B2と式を入力
OKボタンで終了
B2を下方へフィルコピー
前回退所日と同日も記入して良いのであれば数式は=B2-1となります。

退所日も設定出来ますので、A2を選択し、上記のように設定し数式が=A1とすれば良いだけです。

２０１０の日付　入力規則は下記サイトご参照
http://park.geocities.jp/cheflapin/data/data0007

補足

折角ご回答いただき申し訳ありません。
私の説明がなく済みませんでした。

再度添付の図で説明させていただくと
　入所　退所　部屋記号(EとNの二つ)　部屋番号(1111～1120)を２行目、３行目と順次入力します。

　２行目に対して３行目は重複していません。
　２行目、３行目に対して４行目は３行目の５/４～５/６が重複していますので注意を喚起して入力を防止し　たいのです。
　５行目も同様にです。
　この図で言うと５行目以降は重複したサンプルとしてわざと示しました。
　「入所(A列)」「退所(B列)」のそれぞれは図のように色付けですでに注意喚起はできています。

厄介のはわざと入力した６行目です。
「入所(A列)」=０５/０２　「退所(B列)」=０５/０８　が私の方法ではヒットしません。
この期間内の重複を何らかの方法でご教示いただきたいのです。
よろしくお願いします。