Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6012 / 13646 ツリー ←次へ | 前へ→

【47625】二つのデータを見比べて、新リストを作成 Mia 07/3/16(金) 19:31 質問[未読]
【47627】Re:二つのデータを見比べて、新リストを作成 ウッシ 07/3/16(金) 20:15 発言[未読]
【47628】Re:二つのデータを見比べて、新リストを作成 Hirofumi 07/3/16(金) 20:44 回答[未読]
【47629】Re:二つのデータを見比べて、新リストを作成 ichinose 07/3/16(金) 21:07 発言[未読]
【47644】Re:二つのデータを見比べて、新リストを作成 Mia 07/3/17(土) 13:25 質問[未読]
【47645】Re:二つのデータを見比べて、新リストを作成 ウッシ 07/3/17(土) 13:41 発言[未読]
【47646】Re:二つのデータを見比べて、新リストを作成 Kein 07/3/17(土) 13:52 回答[未読]
【47647】Re:二つのデータを見比べて、新リストを作成 Hirofumi 07/3/17(土) 14:01 回答[未読]
【47696】Re:二つのデータを見比べて、新リストを作成 Mia 07/3/19(月) 16:45 質問[未読]
【47698】Re:二つのデータを見比べて、新リストを作成 Kein 07/3/19(月) 17:02 発言[未読]
【47700】Re:二つのデータを見比べて、新リストを作成 Mia 07/3/19(月) 17:59 質問[未読]
【47703】Re:二つのデータを見比べて、新リストを作成 ichinose 07/3/19(月) 18:50 発言[未読]
【47704】Re:二つのデータを見比べて、新リストを作... ichinose 07/3/19(月) 18:52 発言[未読]
【47657】Re:二つのデータを見比べて、新リストを作成 ichinose 07/3/17(土) 19:01 発言[未読]
【47686】Re:二つのデータを見比べて、新リストを作成 Mia 07/3/19(月) 7:33 お礼[未読]

【47625】二つのデータを見比べて、新リストを作成
質問  Mia  - 07/3/16(金) 19:31 -

引用なし
パスワード
   以前にも書き込みをさせていただいたものです。

固定された番号がふってある列と、番号が重複した列を調査し、
新たなリストを作成したいのですが、
私の知識ではできなく、是非お力を貸していただきたいのです。

イメージとしては↓を見てみてください。
*A列→固定された番号
*B列→重複、欠落した番号、A列に含まない番号もあり


A列  B列
01   01
02   02
03   04
04   07
05   07
06   07
07   12

などというものがあるとします。
その場合に、A列の番号を元に、B列で重複してあるものとA列に含まれないものを順番どおりにC列に表示させたいのです。

イメージ

A列  B列   C列
01   01   01
02   04   02
03   04   03
04   04   04
05   07   04
06   07   05
07   12   06
        07
        07
        12

*重複しているものは、重複している分だけ、
B列になく、A列にあるものはC列へ(03のように)、
A列になく、B列にあるものもC列へ(12のように)、
C列では、07から12に突然飛んでいますが、A列に08以降の番号がないため、無視、

というふうにしたいのです。
分かりにくいうえに、複雑なので方法がないかもしれませんが・・・・
どなたか、知恵をかしていただけますでしょうか。お願いいたします。

【47627】Re:二つのデータを見比べて、新リストを...
発言  ウッシ  - 07/3/16(金) 20:15 -

引用なし
パスワード
   こんばんは

結果イメージと説明文、合ってますか?

なんか違うみたいで、訳がわからないです。

【47628】Re:二つのデータを見比べて、新リストを...
回答  Hirofumi  - 07/3/16(金) 20:44 -

引用なし
パスワード
   なんか、質問の説明とイメージが合って無い様な?
A列とB列が一致した場合はどうなるの?
一応、一致した場合も出力しています
尚、A列、B列共に昇順整列済みとします

Option Explicit

Public Sub Extraction()

  'データの列数
  Const clngColumns As Long = 1
  
  Dim i As Long
  Dim lngEnd1 As Long
  Dim vntList1 As Variant
  Dim lngRow1 As Long
  Dim lngEnd2 As Long
  Dim vntList2 As Variant
  Dim lngRow2 As Long
  Dim vntResult As Variant
  Dim lngWrite As Long
  Dim strProm As String
  
  'A列ののA1を基準とします(Listの左上隅)
  With ActiveSheet.Cells(1, "A")
    '行数を取得
    lngEnd1 = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row + 1
    If lngEnd1 <= 1 And .Value = "" Then
      strProm = "データが有りません"
      GoTo Wayout
    End If
    'A列を配列に取得
    vntList1 = .Resize(lngEnd1 + 1, clngColumns).Value
  End With
  
  'B列のB1を基準とする
  With ActiveSheet.Cells(1, "B")
    '行数を取得
    lngEnd2 = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row + 1
    If lngEnd1 <= 1 And .Value = "" Then
      strProm = "データが有りません"
      GoTo Wayout
    End If
    'A、B列を配列に取得
    vntList2 = .Resize(lngEnd2 + 1, clngColumns).Value
  End With
  
  '結果出力用配列を確保
  ReDim vntResult(1 To lngEnd1 + lngEnd2, 1 To 1)
  
  '書き込み行を初期値に(Offse値)
  lngWrite = 0
  'Sheet1のA列の比較位置
  lngRow1 = 1
  'Sheet2のA列の比較位置
  lngRow2 = 1
  'Sheet1のA列若しくは,Sheet2のA列が最終行に達するまで繰り返し
  Do Until lngRow1 > lngEnd1 Or lngRow2 > lngEnd2
    '比較結果に就いて
    Select Case vntList1(lngRow1, 1)
      Case Is = vntList2(lngRow2, 1) 'Matchiした場合
        '出力位置を更新
        lngWrite = lngWrite + 1
        'A列のデータを配列に代入
        vntResult(lngWrite, 1) = vntList1(lngRow1, 1)
        '両データの比較位置の更新
        lngRow1 = lngRow1 + 1
        lngRow2 = lngRow2 + 1
      Case Is > vntList2(lngRow2, 1) 'B列の固有行の場合
        '出力位置を更新
        lngWrite = lngWrite + 1
        'B列のデータを配列に代入
        vntResult(lngWrite, 1) = vntList2(lngRow2, 1)
        'B列の比較位置を更新
        lngRow2 = lngRow2 + 1
      Case Is < vntList2(lngRow2, 1) 'A列の固有行の場合
        '出力位置を更新
        lngWrite = lngWrite + 1
        'A列のデータを配列に代入
        vntResult(lngWrite, 1) = vntList1(lngRow1, 1)
        'A列の比較位置を更新
        lngRow1 = lngRow1 + 1
    End Select
  Loop
  
  '残ったA列の固有値を出力
  For i = lngRow1 To lngEnd1
    '出力位置を更新
    lngWrite = lngWrite + 1
    'データを配列に代入
    vntResult(lngWrite, 1) = vntList1(i, 1)
  Next i
  
  '残ったB列の固有値を出力
  For i = lngRow2 To lngEnd2
    '出力位置を更新
    lngWrite = lngWrite + 1
    'データを配列に代入
    vntResult(lngWrite, 1) = vntList2(i, 1)
  Next i
  
  Application.ScreenUpdating = False
  
  '抽出データを書きこむ位置を指定し結果配列を出力
  With ActiveSheet.Cells(1, "C").Resize(lngWrite)
    .NumberFormatLocal = "@"
    .Value = vntResult
  End With
  
  Application.ScreenUpdating = True
  
  strProm = "処理が完了しました"
  
Wayout:

  MsgBox strProm, vbInformation
  
End Sub

【47629】Re:二つのデータを見比べて、新リストを...
発言  ichinose  - 07/3/16(金) 21:07 -

引用なし
パスワード
   こんばんは。

私は、Hirofumi さんのコードを作動した結果をみてやっと
わかりました。

これは、複雑な規則だと仰られているのなら、
例題を4つぐらいは紹介してください。

私は以下のようなコードにしました。

標準モジュールに
'=========================================================
Sub test()
  Dim rng As Range
  Dim crng As Range
  Dim ans As Variant
  Dim g0 As Long
  ans = Application.Transpose(Range("b1", Cells(Rows.Count, "b").End(xlUp)).Value)
  Set rng = Range("a1", Cells(Rows.Count, "a").End(xlUp))
  For Each crng In rng
    If IsError(Application.Match(crng.Value, ans, 0)) Then
     ReDim Preserve ans(1 To UBound(ans) + 1)
     ans(UBound(ans)) = crng.Value
     End If
    Next
  For g0 = LBound(ans) To UBound(ans)
    Cells(g0, 3).Value = Application.Small(ans, g0)
    Next
End Sub

問題のシートをアクティブにして実行してみてください。

【47644】Re:二つのデータを見比べて、新リストを...
質問  Mia  - 07/3/17(土) 13:25 -

引用なし
パスワード
   分かりにくい説明にも関わらず、教えていただいてありがとうございます。
そしてお時間をとらせてしまって本当にすみません。

具体的なイメージで説明してみます。まず、A列とB列は全く別物と考えます。

例えば、会社説明会のセミナーがあるとします。
そこで「事前参加者予定リスト」をA列に、そして、
「実際に参加した人たち」をB列に入れるようになります。

<図>

A列       B列
(参加予定者)  (実際の参会者)
01-Aさん     01-Aさん
02-Bさん     03-Cさん
03-Cさん     03-Cさん
04-Dさん     06-Fさん 
05-Eさん     11-Jさん(突然参加者)
06-Fさん     11-Jさん(突然参加者)
07-Gさん     13-Lさん(突然参加者)


つまり、A列は事前参加者リストなので、同じ数字が重複することは絶対にありません。
しかしB列は、
・一度会場に入った人が出て、また同じ人が入ってきた時もカウントすること(CさんやJさんのように)
・参加予定者以外の人が、突然参加することがある

ということなのです。

つまりC列には、A列の参加予定者全員を含め、B列で突然入ってきた人、
数回入ってきた人を順番どおりに表示しなくてはならないんです。
つまり、参加予定者(参加していてもしていなくても)、実際参加者を入った数だけ書くということです。


<図2>

A列       B列             
(参加予定者)  (実際の参会者)       
01-Aさん     01-Aさん           
02-Bさん     03-Cさん           
03-Cさん     03-Cさん           
04-Dさん     06-Fさん           
05-Eさん     11-Jさん(突然参加者)    
06-Fさん     11-Jさん(突然参加者)    
07-Gさん     13-Lさん(突然参加者)


C列                        
(全リスト)                        
01-Aさん                       
02-Bさん
03-Cさん
03-Cさん
04-Dさん
05-Eさん
06-Fさん
07-Gさん
11-Jさん
11-Jさん
13-Lさん


なお、C列には08〜10番がいなく、突然07から11に飛んでいますが、
08〜10はA列にもB列にもいないので問題ありません。

今回は例なのでA列には07までしか書きませんでしたが、
実際は600人ほどいるので、一つ一つ数を数えながら書いていくのが本当に大変です。
何か、いい案がありましたらご教授ください。

まだ分かりにくい点がありましたら、遠慮なく聞いてください。
よろしくお願いします。

【47645】Re:二つのデータを見比べて、新リストを...
発言  ウッシ  - 07/3/17(土) 13:41 -

引用なし
パスワード
   こんにちは

説明からすると、Hirofumiさんのコードで

  'A列のA2を基準とします(Listの左上隅)
  With ActiveSheet.Cells(2, "A")

と変更するだけで出来るのでは?

【47646】Re:二つのデータを見比べて、新リストを...
回答  Kein  - 07/3/17(土) 13:52 -

引用なし
パスワード
   これでどうでしょーか ?

Sub test()
  Dim C As Range
  Dim Lr As Long, Cnt As Long
 
  Range("C:C").ClearContents
  For Each C In Range("A1", Range("A65536").End(xlUp))
   Lr = Range("C65536").End(xlUp).Row + 1
   Cnt = WorksheetFunction _
   .CountIf(Range("B:B"), C.Value)
   If Cnt < 2 Then
     Cells(Lr, 3).Value = C.Value
   Else
     Cells(Lr, 3).Resize(Cnt).Value = C.Value
   End If
  Next
  For Each C In Range("B1", Range("B65536").End(xlUp))
   If IsError(Application.Match(C.Value, Range("A:A"), 0)) Then
     Range("C65536").End(xlUp).Offset(1).Value = C.Value
   End If
  Next
  Range("C1").Delete xlShiftUp
End Sub

【47647】Re:二つのデータを見比べて、新リストを...
回答  Hirofumi  - 07/3/17(土) 14:01 -

引用なし
パスワード
   通常は、列見出しを基準セルにしていますが、
今回は、データ先頭を基準セルにしていますので、

例で言うなら
A列の基準セルは、A列の「01-Aさん」のセル位置
B列の基準セルは、B列の「01-Aさん」のセル位置

を指定すれば、後は其のままで善いと思いますが?

【47657】Re:二つのデータを見比べて、新リストを...
発言  ichinose  - 07/3/17(土) 19:01 -

引用なし
パスワード
   こんばんは。
データの種類が↓のように 数字-名前
というパターンなら、

>
>A列       B列
>(参加予定者)  (実際の参会者)
>01-Aさん     01-Aさん
>02-Bさん     03-Cさん
>03-Cさん     03-Cさん
>04-Dさん     06-Fさん 
>05-Eさん     11-Jさん(突然参加者)
>06-Fさん     11-Jさん(突然参加者)
>07-Gさん     13-Lさん(突然参加者)

標準モジュールに
'===============================================
Sub test()
  Dim marray As Variant
  Dim rng As Range
  Dim crng As Range
  Dim ans As Variant
  Dim dkey As Variant
  Dim dat As Variant
  Dim num As Long
  Dim g0 As Long
  Dim g1 As Long
  Dim g2 As Long
  If Cells(Rows.Count, "b").End(xlUp).Row > 1 Then
    With CreateObject("scripting.dictionary")
     ans = Application.Transpose(Range("b2", Cells(Rows.Count, "b").End(xlUp)).Value)
     For g0 = LBound(ans) To UBound(ans)
       dkey = Val(Split(ans(g0), "-")(0))
       If .Exists(dkey) Then
        marray = .Item(dkey)
        marray(1) = marray(1) + 1
        .Item(dkey) = marray
       Else
        .Add dkey, Array(ans(g0), 1)
        End If
       Next
     Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
     If rng.Row > 1 Then
       For Each crng In rng
        dkey = Val(Split(crng.Value, "-")(0))
        If Not .Exists(dkey) Then
          .Add dkey, Array(crng.Value, 1)
          End If
        Next
       End If
     g0 = LBound(.Keys)
     g1 = 2
     Do While g0 <= UBound(.Keys)
       dat = .Item(Application.Small(.Keys, g0 + 1))(0)
       num = .Item(Application.Small(.Keys, g0 + 1))(1)
       For g2 = 1 To num
        Cells(g1, 3).Value = dat
        g1 = g1 + 1
        Next
       g0 = g0 + 1
       Loop
     End With
    End If
End Sub

【47686】Re:二つのデータを見比べて、新リストを...
お礼  Mia  - 07/3/19(月) 7:33 -

引用なし
パスワード
   投稿者です。
皆様、色々と教えていただいてありがとうございます。

土日が仕事休みだったため、まだ試していません。。。すみません。
これから出勤しますので試してみますね。
本当にありがとうございます。

【47696】Re:二つのデータを見比べて、新リストを...
質問  Mia  - 07/3/19(月) 16:45 -

引用なし
パスワード
   投稿者です。
実際に全部試して見ましたが、エラーになってしまいました。

Keinさんが作っていただいたものがエラーにならず、実行できましたが、
A列になくて、B列にあるものが抽出されませんでした。

つまり、

A列       B列
(参加予定者)  (実際の参会者)
01-Aさん     01-Aさん
02-Bさん     03-Cさん
03-Cさん     03-Cさん
04-Dさん     06-Fさん 
05-Eさん     11-Jさん(突然参加者)
06-Fさん     11-Jさん(突然参加者)
07-Gさん     13-Lさん(突然参加者)

の中で、A列にあるものは処理されるのですが、
B列の11-Jさん(突然参加者)以降が処理されませんでした。

色々お聞きしてしまって申し訳ないのですが、
B列も処理できるようにするには、どうしたらよいでしょうか?

【47698】Re:二つのデータを見比べて、新リストを...
発言  Kein  - 07/3/19(月) 17:02 -

引用なし
パスワード
   その部分は
>  For Each C In Range("B1", Range("B65536").End(xlUp))
>   If IsError(Application.Match(C.Value, Range("A:A"), 0)) Then
>     Range("C65536").End(xlUp).Offset(1).Value = C.Value
>   End If
>  Next
というコードで処理しているので、Match関数がエラー値を返すところは
ちゃんとC列に転記できるはずなんですが・・。
試しに

Sub Check_Cnt()
  Dim C As Range
  Dim Cnt As Long

  For Each C In Range("B1", Range("B65536").End(xlUp))
   Cnt = WorksheetFunction.CountIf(Range("A:A"), C.Value)
   Debug.Print C.Value & " : " & Cnt
  Next
End Sub

を実行して、イミディエイトウィンドウで値とA列での一致数をチェック
してみて下さい。一致数が 0 になっているものがあれば、以前のコードを

>If IsError(Application.Match(C.Value, Range("A:A"), 0)) Then

If WorksheetFunction.CountIf(Range("A:A"), C.Value) = 0 Then

というように変更して、やってみて下さい。
あと、他の回答者のコードについても、ひとつづつエラーになった個所を
指摘して答えをもらうようにして下さい。

【47700】Re:二つのデータを見比べて、新リストを...
質問  Mia  - 07/3/19(月) 17:59 -

引用なし
パスワード
   教えていただいたとおりに試しましたが、やはり、
A列にないものでB列にあるものがC列に表示されません。

A列の最後の数字が、39-LLさんだとして、
B列に、40-YYさんがいるとすると、40-YYさんがC列に表示されません。

他の方のももう一度試してみます。その結果をまた報告しますね。

【47703】Re:二つのデータを見比べて、新リストを...
発言  ichinose  - 07/3/19(月) 18:50 -

引用なし
パスワード
   ▼Mia さん:
こんばんは。

>実際に全部試して見ましたが、エラーになってしまいました。
私も他の方のコードも全部試しました。
きちんとC列にデータが表示されていましたよ!!
(Win2000&Excel2002で確認)

こういう場合、想定しているデータがMia さんと回答者では違う
又は、試行データに想定以外のデータがある場合をよく経験します。


で、サンプルデータもこちらで作ってしまいます。


新規ブック(Sheet1というシート名が存在する)の標準モジュールに

'=================================================================
Option Explicit
Sub main()
  Call mk_sample 'サンプルデータを作成
  MsgBox "ご覧のデータで処理をします"
  Call test
End Sub
'=================================================================
Sub mk_sample()
  With Worksheets("sheet1")
    .Activate
    .Cells.Clear
    .Range("a1:c1").Value = Array("参加予定者", "実際の参会者", "延べ参加者?")
    .Range("a2:a8").Value = Application.Transpose(Array( _
            "01-Aさん", "02-Bさん", _
            "03-Cさん", "04-Dさん", _
            "05-Eさん", "06-Fさん", _
            "07-Gさん"))
    .Range("b2:b8").Value = Application.Transpose(Array( _
            "01-Aさん", "03-Cさん", _
            "03-Cさん", "06-Fさん", _
            "11-Jさん(突然参加者)", _
            "11-Jさん(突然参加者)", _
            "13-Lさん(突然参加者)"))
    End With
End Sub
'=====================================================================
Sub test()
  Dim marray As Variant
  Dim rng As Range
  Dim crng As Range
  Dim ans As Variant
  Dim dkey As Variant
  Dim dat As Variant
  Dim num As Long
  Dim g0 As Long
  Dim g1 As Long
  Dim g2 As Long
  If Cells(Rows.Count, "b").End(xlUp).Row > 1 Then
    With CreateObject("scripting.dictionary")
     ans = Application.Transpose(Range("b2", Cells(Rows.Count, "b").End(xlUp)).Value)
     For g0 = LBound(ans) To UBound(ans)
       dkey = Val(Split(ans(g0), "-")(0))
       If .Exists(dkey) Then
        marray = .Item(dkey)
        marray(1) = marray(1) + 1
        .Item(dkey) = marray
       Else
        .Add dkey, Array(ans(g0), 1)
        End If
       Next
     Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
     If rng.Row > 1 Then
       For Each crng In rng
        dkey = Val(Split(crng.Value, "-")(0))
        If Not .Exists(dkey) Then
          .Add dkey, Array(crng.Value, 1)
          End If
        Next
       End If
     g0 = LBound(.Keys)
     g1 = 2
     Do While g0 <= UBound(.Keys)
       dat = .Item(Application.Small(.Keys, g0 + 1))(0)
       num = .Item(Application.Small(.Keys, g0 + 1))(1)
       For g2 = 1 To num
        Cells(g1, 3).Value = dat
        g1 = g1 + 1
        Next
       g0 = g0 + 1
       Loop
     End With
    End If
End Sub


これでmainを実行してみてください。
testというプロシジャーは、前回投稿のままです。
mk_sampleでMia さんの投稿を基にしたサンプルデータを作成しています。

これでC列にデータが作成されるか試してみてください。

これが動作するようなら、ご自分の動作しないデータと何が違うのか
見比べてください。

【47704】Re:二つのデータを見比べて、新リストを...
発言  ichinose  - 07/3/19(月) 18:52 -

引用なし
パスワード
   尚、mainを実行したC列の結果は、

延べ参加者?
01-Aさん
02-Bさん
03-Cさん
03-Cさん
04-Dさん
05-Eさん
06-Fさん
07-Gさん
11-Jさん(突然参加者)
11-Jさん(突然参加者)
13-Lさん(突然参加者)


こんな結果が表示されています。

6012 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free