Excelで作成した表の中から重複している数値を知りたい

質問

仕事でExcelで作成した表について教えていただきたいことがあります。
項目がたくさんある表なのですが、そのなかのひとつの項目で、重複する番号がたくさんある項目があるのですが、どれが重複した番号かを知りたいのです。500行くらいになる表なので、作業が大変なのですが・・・。現在はいったん、その項目を番号順にデータの並べ替えをしてから、データの集計を行い、番号の重複している行を色分けして、またデータの並べ替えで元の行番号順に戻し、色がついている行が重複しているんだなと思いながら作業をしています。
かなりの行数なのでその作業がすごく大変です。
関数などよくわからないのですが、何か簡単にできる方法がないかなといつも考えています。
例えば、並び替えなどをせず、作成している表の横に、その項目の重複している番号を表示する、とか、そういったことができないものでしょうか？
分かりにくい書き方で申し訳ありません・・・。

回答No.6

mshr1962

>色がついている行が重複しているんだなと思いながら作業をしています。
数式は出ているので別のアプローチ
重複を見たい列を選択して、「書式」「条件付き書式」
「数式が」「=COUNTIF($A:$A,$A1)」として書式を押してパターンの色を設定
これだけで重複したセルが塗りつぶせます。

>さらに、重複する数字を表の横に表示したりできないでしょうか？
重複している番号とは行番号のことでしょうか？
一番初めにその数値の出る行番号なら
=IF(COUNTIF(A:A,A1)>1,MATCH(A1,A:A,-1))
1度しかでない場合はFALSEと表示されます。

お礼

ご回答ありがとうございました。みなさんに教えていただいた方法をいろいろ試してみたいと思います。
どうもありがとうございました。

回答No.5

merlionXX

No1です。

> さらに、重複する数字を表の横に表示したりできないでしょうか？

重複してる数値はさきほどの例だと、2以上の答えが出たセルの左がわのセルの数値ですが・・・。

ひょっとして重複してるセルの行を表示したいのですか？
ならば
=IF(COUNTIF($A$1:$A$500,A1)>1,MATCH(A1,A2:$A$500,0)+ROW(),"")
で最初の重複データのところでその行より下にある重複数値がある行を表示します。
それ以上重複行がない場合、#N/Aを返します。

お礼

いろいろ教えていただいてありがとうございました。とても助かりました。

回答No.4

noname#22222

ここは、一つ、VBA の組み込みに挑戦して下さい。
以下の手順通りにやれば、我が家のハスキー犬でもできます。

１、問題のシートのタグを右クリックして下さい。
２、最下位の[コードの表示(V)]をクリックして下さい。
３、（General)と表示されているコンボボックスをプルダウン表示して'Worksheet'を選んで下さい。
４、右の'SelectionChange'を、同様のやり方で'chenge'に変えて下さい。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
***** ここにコピー *****
End Sub

と表示されれば、もうすぐ作業は終了です。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim I　 As Integer　　　　　　　　　　' <--- ココカラ！
　　Dim L　 As Integer
　　Dim R　 As Integer
　　Dim C　 As Integer
　　Dim Msg As String

　　If Len(Target.Value & "") Then
　　　　R = Target.Row
　　　　C = Target.Column
　　　　For I = 1 To 999
　　　　　　If I <> R And Cells(I, C) = Target.Value Then
　　　　　　　　Msg = Msg & I & ","
　　　　　　End If
　　　　Next I
　　　　L = Len(Msg & "")
　　　　If L > 0 Then
　　　　　　MsgBox "行 " & Left$(Msg, L - 1) & " が重複しています。"
　　　　End If
　　End If　　　　　　　　　　　　　　　　　　' <---ココマデ！
End Sub

５、＜ココカラ＞＜ココマデ＞を＜***** ここにコピー *****＞にコピーして下さい。
６、右上の赤いXをクリックしてVBエディターを閉じて下さい。

※これで、A1=1、A2=2、A3=3、A4=1とあってA5=1と入力すると、

行 1, 4 が重複しています。

とのメッセージが表示されます。

お礼

詳しいご回答ありがとうございました。
VBAというのに初めて挑戦してみましたが、私には難しいようでした。せっかくわんちゃんにも分かるほど詳しく教えていただいたのに、すみません・・・。どうもありがとうございました。

回答No.3

mu2011

現在の方法を条件付き関数で行うと次の様になりますが如何でしょうか。

１．表がA1:H500として、範囲を選択
２．書式→条件付き書式
３．「関数が」を選択し、関数を=COUNTIF(A$1:A1,A1)>1入力
４．書式→パターンから色を選択
５．ＯＫ

お礼

回答ありがとうございました。
しばらくこの方法も試してみようと思います。
どうもありがとうございました。

回答No.2

noname#123709

ＶＢＡでよければ・・・。
状態が明記されていないので勝手に設定しました。
・最初にB列の値を削除します。
・A列の値の重複を判断します。
・B列にその行が重複している場合に最初に出現した値を親としてその親の行番号を入力します。

Sub test()
Dim i As Long, j As Long
Columns("B").ClearContents
For j = 1 To Range("A65536").End(xlUp).Row
For i = Range("A65536").End(xlUp).Row To j + 1 Step -1
If Cells(j, 1).Value = Cells(i, 1).Value And Cells(i, 1).Offset(, 1).Value = "" Then
Cells(j, 1).Offset(, 1).Value = j
Cells(i, 1).Offset(, 1).Value = j
End If
Next i
Next j
End Sub

お礼

詳しい回答ありがとうございました。
VBAに初めて挑戦してみたのですが、ちょっと私にはむずかしかったようです・・・。
どうもありがとうございました。

回答No.1

merlionXX

仮にA1からA500までデータだとして
B1に
=COUNTIF($A$1:$A$500,A1)
といれて、下までずずーっとコピーしてください。
答えが１なら重複していません。２以上ならその数だけ範囲内に同一の値があります。

補足

回答ありがとうございます。
早速教えていただいた関数を入力すると、１と２で重複している数字が分かりました。
さらに、重複する数字を表の横に表示したりできないでしょうか？
ややこしい質問でごめんなさい。