エクセルでのセルデータの分割方法

質問

エクセル初心者ですのでわかりやすく教えていただけたらと思います。
仕事上急にデータの変更がありましたので早急に対応する必要に迫られています。

　エクセル上、シート1と2にデータがあったとします。
　シート1のデータからシート2にあるデータの共通する部分と共通する部分を除いたセル内のデータを別のセルに分けて作成したいと思います。また、毎日使用するので、VBAもしくはマクロで作成する場合どのようにしたらよいでしょうか？
教えていただけないでしょうか？
何万件もあるデータの中から照合して共通部分と共通部分でないデータを別々のセルに分けられればと思います。

見えにくいですがサンプル画像を添付しておきます。

アドバイスをよろしくお願いいたします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

tom04 · ベストアンサー

No.1です。
補足に
＞1行目にはタイトルが入っています。
＞シート1のc列に商品管理番号が並んでいて、
＞シート2のB列には商品管理番号の一部が並んでいるのですが、
＞シート1のC列とシート２のB列にある共通の部分を除いた数字もしくはアルファベットをシート1のDの列に表示
＞共通の部分はシート１のEセルに表示できればと思います。

というコトですので↓のコードに変更してマクロを実行してみてください。
（操作方法は前回と一緒です）

Sub Sample2() 'この行から
Dim i As Long, k As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
For i = 2 To wS1.Cells(Rows.Count, 3).End(xlUp).Row '←Sheet1の2行目～C列最終行まで（3は列番号でC列）
For k = 2 To wS2.Cells(Rows.Count, 2).End(xlUp).Row '←Sheet2の2行目～B列最終行まで（2は列番号でB列）
If InStr(wS1.Cells(i, 3), wS2.Cells(k, 2)) > 0 Then 'Sheet1のC列にSheet2のB列文字列が含まれている場合は
With wS1.Cells(i, 3)
.Offset(, 1) = Replace(wS1.Cells(i, 3), wS2.Cells(k, 2), "")
.Offset(, 2) = wS2.Cells(k, 2)
End With
End If
Next k
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　1行・1列でも違った場合は予想外の表示になったり、何も表示されないという状態になりますので、
実データの配置を確認してみてください。m(_ _)m

お礼

データを減らして実行してみたらうまくいきました。
親切な回答ありがとうございます。
大変助かりました。

補足

親切に教えていただいてありがとうございます。
上記に記されているように実行したのですが、実行するとしばらくして画面が白くなって水色のリングが回りっぱなしになり”応答なし”と表示されるのですが、その場合データ量が多すぎて処理ができないのでしょうか？

当方初心者なものでよろしくお願いいたします。

回答No.5

keithin

sub macro2()
　worksheets("Sheet1").select
　application.screenupdating = false
　range("D:D").insert shift:=xlshifttoright
　range("D2:D" & cells(rows.count, "D").end(xlup).row).formula = "=COUNTIF(Sheet2!B:B,C2)"

　application.calculation = xlcalculationmanual
　range("D:D").autofilter field:=1, criteria1:=0
　range("B:B").copy range("F1")
　range("F1") = "除く"

　range("C:C").autofilter field:=1, criteria1:="<>0"
　range("B:B").copy range("E1")
　range("E1") = "共通"
　activesheet.autofiltermode = false
　range("D:D").delete shift:=xlshifttoleft
　application.calculation = xlcalculationautomatic
　application.screenupdating = true
end sub

＞あわてて説明が足りていませんでした。

それで？
寄せられた回答を自分で工夫してみるつもりはさらさら無くて、後出しの情報でまた書き直してくれという事ですね。

お礼

ありがとうございました。
今後基礎から勉強してまいります。

補足

不快な思いをさせて申し訳ありません。
当方初心者で申し訳ございません。
この問題が解決したら基本から学習しなおしてみます。

回答No.3

aokii

マクロはややこしいので、普通のエクセルでしたら、

別のシートに共通する部分のデータを作るには、
=IF(ISNA(VLOOKUP(Sheet1!A1,Sheet2!A:A,1,FALSE)),"",VLOOKUP(Sheet1!A1,Sheet2!A:A,1,FALSE))
で並べ替え

別のシートに共通する部分を除いたデータを作るには、
=IF(ISNA(VLOOKUP(Sheet1!A1,Sheet2!A:A,1,FALSE)),Sheet1!A1,"")
で並べ替え

ですが、何万件もあるデータでしたら、ExcelではなくAccessをお勧めします。

お礼

ありがとうございました。

補足

回答ありがとうございます。
あわてて説明が足りていませんでした。
シート1のB列に商品管理番号が並んでいて、シート2には商品管理番号の一部が並んでいるのですが、シート１と２の商品番号の並び順には共通性がないなかでシート1のA列にある共通の部分を除いた数字を表示させたいと思います
また、共通の部分は共通の部分で別セルに表示できればと思います。
説明がうまく出来ずにすみません。

回答No.2

keithin

＞何万件もある

のでしたらホントはadvancedfilterの方がいいんですが。

sub macro1()
　worksheets("Sheet1").select
　range("1:1").insert shift:=xlshiftdown
　range("B1") = "Title"
　range("C1") = "Check"
　range("C2:C" & cells(rows.count, "B").end(xlup).row).formula = "=COUNTIF(Sheet2!A:A,B2)"

　application.calculation = xlcalculationmanual
　range("C:C").autofilter field:=1, criteria1:=0
　range("B:B").copy range("F1")
　range("F1") = "NOT FOUND"

　range("C:C").autofilter field:=1, criteria1:="<>0"
　range("B:B").copy range("E1")
　range("E1") = "EXIST"
　activesheet.autofiltermode = false
　range("C:C").clearcontents
　application.calculation = xlcalculationautomatic
end sub

＃ふつーなら、１行目にタイトル行、２行目からデータといった作り方をするのが常識ですが、そういう具合にシートを作成できていない前提のマクロです。
適切に修正して利用してください。

補足

あわてて説明が足りていませんでした。
1行目にはタイトルが入っています。
シート1のc列に商品管理番号が並んでいて、シート2のB列には商品管理番号の一部が並んでいるのですが、シート１と２の商品番号の並び順には共通性がないなかでシート1のC列とシート２のB列にある共通の部分を除いた数字もしくはアルファベットを別のセルこの場合だとシート1のDの列に表示させたいと思います
また、共通の部分は共通の部分で別にシート１のEセルに表示できればと思います。
説明がうまく出来ずにすみません。

回答No.1

tom04

こんにちは！
VBAになってしまいますが、一例です。

＞共通部分と共通部分でないデータを別々のセルに・・・

とありますが、どこに表示すれば良いのか判らないので、
Sheet1のC列に共通部分を！
Sheet1のD列に共通部分でないデータを表示するようにしてみました。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
For i = 1 To wS1.Cells(Rows.Count, 2).End(xlUp).Row
For k = 1 To wS2.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 2), wS2.Cells(k, 1)) > 0 Then
With wS1.Cells(i, 2)
.Offset(, 1) = wS2.Cells(k, 1)
.Offset(, 2) = Replace(wS1.Cells(i, 2), wS2.Cells(k, 1), "")
End With
End If
Next k
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

補足

あわてて説明が足りていませんでした。
1行目にはタイトルが入っています。
シート1のc列に商品管理番号が並んでいて、シート2のB列には商品管理番号の一部が並んでいるのですが、シート１と２の商品番号の並び順には共通性がないなかでシート1のC列とシート２のB列にある共通の部分を除いた数字もしくはアルファベットを別のセルこの場合だとシート1のDの列に表示させたいと思います
また、共通の部分は共通の部分で別にシート１のEセルに表示できればと思います。
説明がうまく出来ずにすみません。