列A 列B 列C 列D 列E 列F 列G 列H
行1 愛 青 パンチ
行2 愛 勇気 友達 BB AA CC
行3 勇気 愛 友達 AA CC BB
行4 勇気 弁護士 正義
行5 勇気 愛
行6 コイ ダイス ダチ
行7 ダイス ダチ コイ
行8 友達 愛 勇気 CC AA BB
上記のような6列のデータ(データによってはB列で終わっていることもあります。)
の順不同で行での重複をチェックする関数を知りたいです。
行2、3、8と行6、7が順番は違いますが同じデータなので重複と出したいです。
こちらのサイトで色々聞いたので、3列の場合には
E1に↓を入れて下にコピー
=IF(COUNTA(A1:C1)=0,"",A1&"_"&B1&"_"&C1&"_"&COUNTA(A1:C1))
D1に↓を入れて下にコピー
=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E$1:E1,A1,""),B1,""),C1,"")="___"&COUNTA(A1:C1)))>1,"重複","")
上記のように教わり、6列のデータが出てきてしまったので
それに対応できるように、
列G1行目に
=IF(COUNTA(A1:F1)=0,"",A1&"_"&B1&"_"&C1&"_"&D1&"_"&E1&"_"&F1&"_"&COUNTA(A1:F1))
こちらの関数を入れ下にドラッグし、
列H1行目には
=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H$1:H1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="___"&COUNTA(A1:F1)))>1,"重複","")
上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。
列A 列B 列C 列D 列E 列F 列G 列H
行1 愛 青 パンチ
行2 愛 勇気 友達 BB AA CC
行3 勇気 愛 友達 AA CC BB 重複
行4 勇気 弁護士 正義
行5 勇気 愛
行6 コイ ダイス ダチ 重複
行7 ダイス ダチ コイ 重複
行8 友達 愛 勇気 CC AA BB 重複
3列の時にはできた関数なので6列も可能だと思うのですが何がいけないのか調べていたら
こんな時間になってしまい、助けをお願いしたく再度書き込みしております。
何卒よろしくお願いいたします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
ANo.3です
> 教えて頂いたものですと正しく重複チェックできないものがありました。
それは失礼。
どの様なパターンの時でしょう?
提示例の「行6」の重複は単なる記入ミスかと思いましたが、「行6」にも重複が表示されることがお望みなのでしょうか。
No.4
- 回答日時:
回答No.3で誤動作の指摘がありましたので訂正させて頂きます。
G1=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1
データの区切りにスペースを入れました。
H1=IF(SUMPRODUCT((TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,""))="")*1)>1,"重複","")
H1セルでは区切りの空白が残るので全てがマッチしたときはTRIM関数でスペースを除去すると文字列数が0("")になります。
チェックするデータの個数に関係なく比較対象の文字数が0になったとき重複と見做すようにすれば良いでしょう。
No.3
- 回答日時:
画面だと見づらい「_(アンダースコア)」を使ったせいで紛らわしかったですね。
すでにNo.1の方が指摘されている内容ではありますが……
G1=IF(COUNTA(A1:F1)=0,"",A1&"_"&B1&"_"&C1&"_"&D1&"_"&E1&"_"&F1&"_"&COUNTA(A1:F1))
H1=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="______"&COUNTA(A1:F1)))>1,"重複","")
ただ、No.1の方の回答の方がシンプルですが、
・「勇気、愛」と言う行と「勇気愛」と言う行が有った場合、重複と見なされる
・「勇気、愛」と言う行と「勇気、愛、恋」と言う行が有った場合、重複と見なされると言う問題が有ります。
No.2
- 回答日時:
こんにちは!
VBAになってしまいますが、一例です。
何列あっても対応できるようにしてみました。
元データはSheet1のA1セルからあるとし、A列だけは最終行まで必ずデータが入っているとします。
尚Sheet2を作業用のSheetとして使用していますので、
Sheet2は使っていない状態にしておいてください。
Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, lastRow As Long, lastCol As Long
Dim c As Range, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
lastCol = 1
Do While WorksheetFunction.CountA(.Columns(lastCol)) > 0
lastCol = lastCol + 1
Loop
Set c = .Columns(lastCol - 1).Find(what:="重複", LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
lastCol = lastCol - 1
Else
.Columns(lastCol - 1).ClearContents
lastCol = lastCol - 2
End If
lastRow = .UsedRange.Rows.Count
Range(.Cells(1, "A"), .Cells(lastRow, lastCol)).Copy wS.Range("B1")
For i = 1 To wS.Cells(Rows.Count, "B").End(xlUp).Row
j = wS.Cells(i, Columns.Count).End(xlToLeft).Column
Range(wS.Cells(i, "B"), wS.Cells(i, j)).Sort key1:=wS.Cells(i, "B"), order1:=xlAscending, _
Header:=xlNo, Orientation:=xlLeftToRight
For k = 2 To j
If wS.Cells(i, k) <> "" Then
wS.Cells(i, "A") = wS.Cells(i, "A") & wS.Cells(i, k) & "_"
End If
Next k
Next i
For i = 1 To lastRow
If WorksheetFunction.CountIf(wS.Range("A:A"), wS.Cells(i, "A")) > 1 Then
.Cells(i, lastCol + 1) = "重複"
End If
Next i
wS.Cells.Clear
End With
End Sub 'この行まで
こんな感じではどうでしょうか?m(_ _)m
No.1
- 回答日時:
>上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。
タイプミスが有ることと単純な見落としがあります。
タイプミス部分
H1=[省略]SUBSTITUTE(H$1:H1,A1,"")[省略] 自身のセルを参照のため循環になります。
H1=[省略]SUBSTITUTE(G$1:G1,A1,"")[省略]
見落とし部分
H1=[省略]="___"&COUNTA(A1:F1)[省略] アンダースコア(_)の数が不足しています。
元データが3列増えているのでG列のデータにはアンダースコアが3文字増えます。
従って、比較対象が全て不一致となり重複なしの判定になります。
単純ミスを防止するには不要な情報を介在させないことを推奨します。
G1=A1&B1&C1&D1&E1&F1
H1=IF(SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="")*1)>1,"重複","")
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
これ何て呼びますか Part2
あなたのお住いの地域で、これ、何て呼びますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excel 順不同数値の検索
Excel(エクセル)
-
エクセルで2列の順序が違うデータの照合方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの警告について
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
Excelでの文字色
-
ワークシートに出現したこの画...
-
EXCELの散布図で日付が1900年に...
-
OFFSET関数を使用したいのです...
-
エクセルでファイルの最終更新...
-
エクセルの文字が途中から消える
-
エクセルデーターから必要な項...
-
Excel 大小比較演算子による「...
-
SUBTOTALは、参照された数字で...
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
Excelについて教えてください。...
-
エクセルVBA 月の中で、月~土...
-
Excelの数字の前に入っている空...
-
Excelの関数について このよう...
-
セルの数を求めたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報