|
▼VBAビギナー さん:
独覚さんからの関数処理で充分かとは思いますがVBA処理としての一例です。
元シートが "Sheet1"、結果を "Sheet2" に表示します。
Sub Sample()
Dim MAXQ As Object
Dim OKNG As Object
Dim CONT As Object
Dim c As Range
Set MAXQ = CreateObject("Scripting.Dictionary")
Set OKNG = CreateObject("Scripting.Dictionary")
Set CONT = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
If MAXQ.exists(c.Value) Then
If c.Offset(, 1).Value > MAXQ(c.Value) Then MAXQ(c.Value) = c.Offset(, 1).Value
If c.Offset(, 2).Value <> CONT(c.Value) Then OKNG(c.Value) = "Error"
Else
MAXQ(c.Value) = c.Offset(, 1).Value
OKNG(c.Value) = "OK"
CONT(c.Value) = c.Offset(, 2).Value
End If
Next
End With
With Sheets("Sheet2")
.UsedRange.ClearContents
.Range("A1:C1").Value = Array("製品", "最大重量", "チェック")
.Range("A2").Resize(MAXQ.Count).Value = WorksheetFunction.Transpose(MAXQ.Keys)
.Range("B2").Resize(MAXQ.Count).Value = WorksheetFunction.Transpose(MAXQ.Items)
.Range("C2").Resize(MAXQ.Count).Value = WorksheetFunction.Transpose(OKNG.Items)
.Select
End With
End Sub
|
|