[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『重複データを抽出したい』(たく)
二つのシートを比較して重複しているデータを抽出したいのですが関数がよくわからずアドバイスをいただけたら幸いです。
生産番号 コード
123 ABC
123 ABB
123 ABA
123 AB
124 ABC
124 AB
124 ABB
上記のようなシートとコードの書いた一覧のシートがあります。
まず別シートのA1に生産番号が入力されていて、その番号の中でのコードがコード一覧シートのA列のコードにあるものをA2、A3に表示させたいと考えています。
上の例ですと、別シートのA1に123と入力されてた生産番号の中でABBとABAがコード一覧にあったするとそれをA2、A3に表示させるようにしたいと思っています。
SMAL等を使うかと思いますが、複雑でよく理解できていません。
アドバイスよろしくお願いします。
< 使用 Excel:Office365、使用 OS:Windows10 >
(黄色い循環参照) 2019/09/28(土) 01:43
sheet1のA1で指定されたものと同じ番号が記載されている
sheet2生産番号の、B列にあるコードのうち
sheet3の生産番号、コードと同じものを探す
ということで合っていますか?
sheet3のコード表はA2から100くらいとありますが、
「生産番号123の中でABBとABAがsheet3に有ったと仮定」
というデータはどのように記載されているのでしょうか?
sheet3のA列にすべて記載されているのでしょうか?
それともsheet3のB列以降にも分けて記載されているのでしょうか?
(黄色い循環参照表) 2019/09/28(土) 10:34
こんなワークシートレイアウトなのね。(説明を簡潔にして欲しい)
| コード表 | 得たい結果表 <<Sheet2>> | <<Sheet3>> | <<Sheet1>> A列 B列 | A列 | A列 1 生産番号 コード | コード | 123 ←これが入力 2 123 ABC | ABB | ABB ←2行以降に出力 3 123 ABB | ABA | ABA 4 123 ABA | BBB | 5 123 AB | CCC | 6 124 ABC | CCD | 7 124 AB | DD | 8 124 ABB | DE | 9 | EEE |
要するに抽出なので、フィルタオプションを使うと簡単かもしれない。 こんな感じの対応関係にすればいい。
"リスト範囲" "検索条件範囲" "抽出範囲" 生産番号 コード 生産番号 コード 生産番号 コード 123 ABC 123 ABB 123 ABB 123 ABB ABA 123 ABA 123 ABA BBB 123 AB CCC 124 ABC CCD 124 AB DD 124 ABB DE EEE
私のお薦めは、フィルタオプション(を使ったマクロ)ということになるが、 どうしてもワークシート関数でということなら、配列数式で E2:E8に以下の式を設定する。(入力してCtrl+Shift+Enterを押す)
=IFERROR( INDEX( $B$2:$B$8, SMALL( IF(($A$2:$A$8=$F$1)*(COUNTIF($D$2:$D$9,$B$2:$B$8)>0), ROW($A$1:$A$7), 1000 ),ROW()-1 ) ), "" )
A列 B列 C列 D列 E列 1 生産番号 コード コード 123 ←これが入力 2 123 ABC ABB ABB ←2行以降に出力 3 123 ABB ABA ABA 4 123 ABA BBB 5 123 AB CCC 6 124 ABC CCD 7 124 AB DD 8 124 ABB DE 9 EEE
当方の負荷軽減のためにひとつのワークシートで書いたが、 行の長さ等も含め、実際に合わせて修正してください。
私の不得意分野なので、上記の式は、識者からみると "噴飯物"である可能性が高い。 (γ) 2019/09/28(土) 13:37
ちょっと確認
1)Sheet1 に生産番号を入力するのは A1セルだけ?
2)Sheet2 の表は何行目から何行目にあるんですか?
3)Sheet2 に作業列を使ってもいいですか?
とりあえず以上です (笑) 2019/09/28(土) 15:52
2)sheet2としてしまいましたが、実際の参照は別ファイルで2行目から終わりは変化します。
ファイルの更新でデータ量が変わるため。
3)sheet2のようなデータは毎回(毎日)更新するので作業列は作らない方法を考えてます。
(γ)さんのような配列関数を考えてました。
こちらの方法も確認中ですが、希望と違う値(ABC)が表示されたので式をもう一度確認中です。
(たく) 2019/09/28(土) 20:15
ひとつだけ言っておきます。
COUNTIF で別ファイルを参照する場合 そのファイルを開けておく必要があります。
以上 (笑) 2019/09/29(日) 10:33
{=IFERROR(INDEX(sheet2!$K$2:$K$3193,MATCH(LARGE((sheet2!$C$2:$C$3193=$A1)*(COUNTIF(sheet3!$A$2:$A$138,sheet2!$K$2:$K$3193))/ROW(sheet2!$K$2:$K$3193),ROWS($I$5:$I$5)),1/ROW(sheet2!$K$2:$K$3193),0),COLUMNS($I$5:I$5)),"")}
Sheet1 B1に上記式でB2,B3,B4と同じような式でSHEET2の参照列が変わっています。
Shee3の行数は基本変わりませんが、Sheet2の行数が3193となってますが実際は日ごとの更新で変わるため
変える必要があります。
マクロによる方法等もありましたら、教えていただきたいです。
よろしくお願いします。
(たく) 2019/10/06(日) 10:28
γさんから
>フィルタオプションを使っても、できます。
とコメントがありましたが、それではだめなのでしょうか。
(マナ) 2019/10/06(日) 11:34
>私のお薦めは、フィルタオプション(を使ったマクロ)ということになるが、
手作業で、できたのなら、その作業をマクロに変換すればよいです。
(マナ) 2019/10/06(日) 13:31
(マナ) 2019/10/06(日) 15:03
A列 B列 C列 D列 E列 ・・・ 1 生産番号 コード B列バーコード コード D列バーコード 2 123 ABB :::: ABC :::: 3 124 ABB :::: ABA :::: 4 121 ABA :::: ・ ・ ・ 最終の希望はこんな感じでB、D、F、G列に結果を出したいのですが、 A列の値をsheet2の C列から探して、その中でK列を参照します。 この条件の中で、K列とsheet3のA列を比較して、あるものをすべて抽出するのですが、 最大2個あって一つ目をB列、二つ目をD列に表示する形になります。 (たく) 2019/10/06(日) 16:06
(マナ) 2019/10/06(日) 16:16
C列、K列は同じようにsheet2のL、M列のデータを抽出したいと思っています。 (たく) 2019/10/06(日) 16:36
そうではなくて、ブック名です。
2つのブックがあるという話だったのでは?
> C列、K列は同じようにsheet2のL、M列のデータを抽出したいと思っています。
K列は、どうでもよいです。
>最大2個
ということなら、知りたいのはC列とE列です。
C列とE列は、空欄という意味でしょうか。
それとも、数式が入力されているのでようか。
(マナ) 2019/10/06(日) 16:53
>C列、K列は同じようにsheet2のL、M列のデータを抽出したいと思っています。
これには、対応していません。できる目処は立っているのでしょうか。
それとも追加要望になるのでしょうか。
(マナ) 2019/10/06(日) 17:01
C列とE列と書いたつもりがK列と書いてしまいました。 容易に変更出来そうなら自分で考えたいと思いますが、追加の分を入れるとコードは難しくなりますか?
(たく) 2019/10/06(日) 17:11
(マナ) 2019/10/06(日) 17:19
C列とE列はB列とD列の値で"リスト"ファイルのLとM列の値を表示させたいです。 (たく) 2019/10/06(日) 17:52
間違いないですか。
L列とM列の両方の値を結合させるということでしょうか???
(マナ) 2019/10/06(日) 18:23
Sub test() Dim dic As Object Dim wbリスト As Workbook Dim wb入力 As Workbook Dim v Dim rngD As Range Dim rngC As Range Dim k As Long Dim pCode As String, bCode As String, lotNo Dim n As Long
Set wbリスト = Workbooks("リスト,xlsx") Set wb入力 = ThisWorkbook
v = wbリスト.Sheets("sheet2").Cells(1).CurrentRegion.Value Set rngD = wb入力.Sheets("sheet1").Cells(1).CurrentRegion.Resize(, 1) Set rngC = wb入力.Sheets("sheet3").Columns(1)
Set dic = CreateObject("scripting.dictionary")
For k = 2 To rngD.Rows.Count Set dic(rngD(k, 1).Value) = CreateObject("system.collections.arraylist") Next
For k = 2 To UBound(v) pCode = v(k, 2) If WorksheetFunction.CountIf(rngC, pCode) Then lotNo = v(k, 1) If dic.exists(lotNo) Then bCode = v(k, 12) & v(k, 13) dic(lotNo).Add pCode dic(lotNo).Add bCode End If End If Next
For k = 2 To rngD.Rows.Count lotNo = rngD(k, 1).Value n = dic(lotNo).Count If n > 0 Then rngD(k, 2).Resize(, n).Value = dic(lotNo).toarray Next
End Sub
(マナ) 2019/10/06(日) 18:47
(マナ) 2019/10/06(日) 22:40
Dim dic As Object
Dim wbリスト As Workbook Dim wb入力 As Workbook Dim v Dim rngD As Range Dim rngC As Range Dim k As Long Dim pCode As String, bCode As String, lotNo Dim n As Long
Set wbリスト = Workbooks("リスト,xlsx") Set wb入力 = ThisWorkbook
v = wbリスト.Sheets("sheet2").Cells(1).CurrentRegion.Value Set rngD = wb入力.Sheets("sheet1").Cells(5,6).CurrentRegion.Resize(, 1) Set rngC = wb入力.Sheets("sheet3").Columns(1)
Set dic = CreateObject("scripting.dictionary")
For k = 2 To rngD.Rows.Count Set dic(rngD(k, 1).Value) = CreateObject("system.collections.arraylist") Next
For k = 2 To UBound(v) pCode = v(k, 3) If WorksheetFunction.CountIf(rngC, pCode) Then lotNo = v(k, 1) If dic.exists(lotNo) Then bCode = v(k, 14) & v(k, 15) dic(lotNo).Add pCode dic(lotNo).Add bCode End If End If Next
For k = 5 To rngD.Rows.Count lotNo = rngD(k, 1).Value n = dic(lotNo).Count If n > 0 Then rngD(k, 2).Resize(, n).Value = dic(lotNo).toarray Next
End Sub
< pCode = v(k, 2)
ですがC列参照ですので pCode = v(k, 3)にしましたが違いますでしょうか?
上記コードで行ごとに実行で確認しているのですが最初の
Set wb入力 = ThisWorkbook
でNothingになっているのでまずここが悪いのでしょうか?
(たく) 2019/10/07(月) 05:23
Sheet1も違っていたのですか? 実際のレイアウトについて説明(図示)お願いします。 ~~~~~
(マナ) 2019/10/07(月) 18:41
H列 ••• J列 K列 L列 N列 ・・・ 4 生産番号 コード J列バーコード コード L列バーコード 5 123 ABB :::: ABC :::: 6 124 ABB :::: ABA :::: 7 121 ABA :::: ・ ・ ・
(たく) 2019/10/07(月) 21:18
(マナ) 2019/10/07(月) 21:24
(マナ) 2019/10/07(月) 22:10
(マナ) 2019/10/07(月) 22:32
(マナ) 2019/10/07(月) 22:41
••• C列 ••• K列 ••• N列 O列 ••• 1 生産番号 コード 数値1 数値2 2 123 ABC 300 300 3 123 ABB 300 300 4 123 ABA 2 2 5 123 AB 1 0 6 124 ABC 325 325 7 124 CCD 10 0 8 124 159878 6 6
こんな感じで間の列は空欄ではありません。
(たく) 2019/10/08(火) 02:15
>入力するsheet1の最初の行はF5(検索番号)、入力H5、J5、L5、N5の順になります
(マナ) 2019/10/08(火) 18:31
>入力するsheet1の最初の行はF5(検索番号)、入力H5、J5、L5、N5の順になります
(マナ) 2019/10/08(火) 21:00
(たく) 2019/10/08(火) 22:03
↓これも間違いですか。4娘以上の場合もある?
>最大2個あって
(マナ) 2019/10/08(火) 22:18
>sheet1の最初の行はF5(検索番号)、出力としてH5、J5、L5、N5の順になります 。
L列、N列に出力することはありえないのでは?
(マナ) 2019/10/08(火) 22:43
Sheet1の各列について、Sheet2のどの列のデータが転記されればよいのでしょうか。
再度、説明してください。
(マナ) 2019/10/08(火) 23:43
最初の話には戻りますが、sheet2から転記するのはK列とN列のみです。
ただK列は最大2個なので、それを横に並べます。
これがsheet1のHとK列です。sheet2のN列をsheet1のL列のみかL、N列両方に表示されることになります。
(たく) 2019/10/09(水) 00:14
Sheet1 Sheet2 F列 (生産番号) G列 H列 (コード) <---- K列 I列 (バーコード) <---- N列&O列 J列 (コード) <---- K列 K列 (バーコード) <---- N列&O列
(マナ) 2019/10/10(木) 20:08
こんばんは!
ぱっと見ですけど、、おっしゃてることを順番にコードにしてみました。。。
でも、、番地が一定しない様に思います。
なので、、想像力をFullMaxにしてみました。
先ず、比較するのは、、Sheet2とSheet3ですね???
結果をSheet1に書き出すのですね?
考え方としては、、生産番号とコードを連結したユニークなKeyを作ります。
Sheet3のA列も生産番号と連結してユニークなKeyを作ります。
こうすると、、抽出というよりもあるかないかですね? まぁ、、重複、、抽出と言えないこともないのかな???
番地も飛び飛びでもっと簡潔にデータも取得できるのでしょうけど、、、取り敢えず、、です。。。
あとは、、応用して頂けると助かります。。。
では、、では、、、
一応、、こんな感じです。。。
生産番号 コード J列バーコード コード L列バーコード 123 123 ABC 300 ABB 300
Option Explicit Sub てすと() Dim v() As Variant Dim w() As Variant Dim x As Variant Dim xx As Variant Dim y As Variant Dim z As Variant Dim r As Variant Dim MyKeyA As String Dim MyKeyB As String Dim 生産番号 As String Dim i As Long Dim n As Long Dim k As Long ReDim v(0) With Sheets("Sheet2") x = .Range("C1", .Range("C" & Rows.Count).End(xlUp)).Value y = .Range("C1", .Range("C" & Rows.Count).End(xlUp)).Offset(, 8).Resize(, 1).Value z = .Range("C1", .Range("C" & Rows.Count).End(xlUp)).Offset(, 11).Resize(, 1).Value End With For i = LBound(x, 1) To UBound(x, 1) MyKeyA = x(i, 1) & "," & y(i, 1) r = Application.Match(MyKeyA, v, 0) If IsError(r) Then ReDim Preserve v(n) v(n) = MyKeyA n = n + 1 End If Next 生産番号 = Sheets("Sheet1").Range("F5").Value With Sheets("Sheet3") xx = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value End With k = 0 For i = LBound(xx, 1) To UBound(xx, 1) MyKeyB = 生産番号 & "," & xx(i, 1) r = Application.Match(MyKeyB, v, 0) If Not IsError(r) Then ReDim Preserve w(k) w(k) = y(r, 1) k = k + 1 ReDim Preserve w(k) w(k) = z(r, 1) k = k + 1 End If Next With Sheets("Sheet1") .Range("H5").Value = 生産番号 .Range("J5").Resize(, UBound(w) + 1).Value = w End With Erase v, w, x, xx, y, z End Sub (SoulMan) 2019/10/10(木) 23:06
F列 (生産番号) G列 H列 (コード) <---- K列(1個目) I列 (バーコード) J列 (コード) <---- K列(2個目) K列 (バーコード) L列 <---- N列 M列 (バーコード) N列 <---- O列 O列 (バーコード)
sheet1 バーコード列はバーコードフォントで表示しているだけですのでsheet2からの読み込みはありません。
(たく) 2019/10/11(金) 05:00
>生産番号 = Sheets("Sheet1").Range("F5").Value
>With Sheets("Sheet1")
.Range("H5").Value = 生産番号
ここの生産番号の列がそれぞれ変わってますが問題ないでしょうか?
またこのコードは一つ前のコメントで示した内容と合いますでしょうか?
sheet3と重複したものを抽出するという考えです。
(たく) 2019/10/11(金) 05:15
こんばんは!
試してもないのに何ですけど、、、
基本的にコードというのは、
上から下へ
左から右へ走ります?(読みます?)
なので、、
生産番号 = Sheets("Sheet1").Range("F5").Value
これは、、
生産番号 は、 Sheets("Sheet1").Range("F5").Value です。
と同じです。
逆に↓これは、、
.Range("H5").Value = 生産番号
.Range("H5").Value は 生産番号 です。
となります。
右辺と左辺です。= は、、「は」と読みます。。。普通の日本語です。。。
でも、、この辺のことは最低限、感覚的に理解出来ないと厳しいと思いますよ???
あと、、インデックスが有効範囲にありません。。。
というのは、、多分、、そのシートがないんだと思います。。。
でも、、Sheet1ですよね??
いきなり、、ご自身のBookで試そうとしないで、、新規Bookでお互いが再現出来る状態でお話するのが
こういう掲示板でお話する時の○◇▼ですよね(なって言っていいのか分かりません(^^;)
ちょっと自分でも何いてるのか分からなくなっちゃいましたので、、、今日は、、この辺で、、、
では、、、では、、、 (SoulMan) 2019/10/11(金) 21:27
ちなみにsheet1に表示されてるのはH5のみです。
(たく) 2019/10/12(土) 01:48
おはようございます。。。
レイアウトは例のツールに任せるとして、、
↓この考え方は、、あってますか???
>考え方としては、、生産番号とコードを連結したユニークなKeyを作ります。 >Sheet3のA列も生産番号と連結してユニークなKeyを作ります。
つまり、、うん、、言葉が出ない(^^; やばいぞ、、、まぁ、、そういうことです。。。
この考え方が違うといくらレイアウトを議論しても無駄なので先ずはその確認です。。
あっ、、Sheet 3つも一つも同じなのでできれば、、一つのSheetでやりませんか???
次に、、この考え方があっていたら、、 [[20190108133640]] このトピの中にちょっとふざけたタイトルのコードがありますから それを使ってここへUpしてください。。。
公開されて困る個人名などはふせてください。
データ量も最小限でいいので工夫してください。。。
ご自身で一度、お題を作成してからUpされることをお勧め致します。です。。。
※お馬ちゃんモードに突入する前に、、何としても差し切らなくては(笑) (SoulMan) 2019/10/12(土) 07:47
あとで読み返すとちょっと不親切なのでちょっと補足、、、
Sheet2 C列 K列 N列 生産番号 コード 数値1 123 ABC 300 123 ABB 300 123 ABA 2 123 AB 1 124 ABC 325 124 CCD 10 124 159878 6
Sheet3
A列 コード ABC ABB
Sheet1
F列 1 2 3 4 5 123
以上の様に各Sheetに入力されている状態でコードを走らせると
↓こんな結果になります。。。
123 ABC 300 ABB 300
ちなみに↓これは、 >考え方としては、、生産番号とコードを連結したユニークなKeyを作ります。
Sheet2のC列とK列を連結するということです。 C列&K列 です。
↓これをユニークなKeyといいます。 123&ABC 300 123&ABB 300 123&ABA 2 123&AB 1 124&ABC 325 124&CCD 10 124&159878 6
次に↓これは、 >Sheet3のA列も生産番号と連結してユニークなKeyを作ります。
生産番号とSheet3Aを同じく連結するということです。生産番号&Sheet3A列 です。
↓これもユニークなKeyです。 コード 123&ABC 123&ABB
このユニークなKey同士で比較、検索するのです。 一見、、生産番号の中のコードをとか、、、難しく感じるかもしれませんが、、なれれば単純なことです。。
123&ABC 300 ←これと 123&ABB 300 ←これが該当します。 123&ABA 2 123&AB 1 124&ABC 325 124&CCD 10 124&159878 6
また、現在の抽出結果とかは、、ご希望の状態ではないかもしれませんが、、、
考え方としては、、こんな感じです。
この例では、、3つのシートに分かれていますが、、理屈、、原理、、仕組み さえわかれば
一つのSheet内でも十分再現は可能だと思います。
一つのSheet内で出来ないことが複数のSheet 複数のBookで出来るわけがないと思います。
逆に言えば一つのSheet内で出来れば、複数のSheet 複数のBookでも出来るでしょう。
では、、、では、、、また、、、 (SoulMan) 2019/10/13(日) 17:17
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.