Home>

[vba] how to extract the maximum value for each item in a dynamic array

Question

Extract from CSV data under specific conditions with VBA,
A list of n rows x 4 columns was created using these data.

In the above list,
To obtain a list number that is the maximum value for each item (ex. Maximum value of height in item name A)
How should I code it?

I tried various things such as conditional branching with if statements.
We are struggling to move well. The code is as follows:

The policy is

  1. Create array of n rows x 4 columns with myArray ()
  2. First, create an n-row x 4-column array only when the item name is A
  3. Create an n-row x 1-column array with only length and depth data from the array generated in 2.
  4. Get the largest line number and data in 3.
    I was thinking.
  • The contents of myArray (n) are as follows
    myArray (n)
    L myArray (n, 1)
    L myArray (n, 2)
    L myArray (n, 3)
    L myArray (n, 4)

ex)
myArray (1)
L myArray (1,1) = A
L myArray (1,2) = n1
L myArray (1,3) = 66
L myArray (1,4) = 91
And so on.

  • The code is as follows.
'Range definition
Dim myRange As Range
Set myRange = Range ("A1: An", "D1: Dn")
'Create array myArray
Dim myArray As Variant
myArray = myRange
'Define array
Dim Array_A_len_pre () As Variant
Dim Array_A_len () As Variant
'Loop variable
Dim i As Long
'Store with if statement
For i = 1 To n
  If myArray (i, 1) = "A" Then
'Generate an array of length only'
    ReDim Preserve Array_A_len_pre (i)
    Array_A_len_pre (i) = myArray (i, 4)
  'Loop variable'
    Dim j As Long
    'Remove the empty value of' Array_A_len_pre () and regenerate the array
    For j = 0 Ton
       If Array_A_len_pre (j)<>"" Then
          ReDim Preserve Array_A_len (j)
          Array_A_len (j) = Array_A_len_pre (i)
       End If
    Next
* ======================= Below are the questions: ===================

  End If
Next

I would appreciate your teaching.

vba csv array
  • Answer # 1

    An example using a dictionary (associative array).
    I created it with n = 5.

    Public Sub test ()
    'Range definition
    Dim n As Long
    n = 5
    Dim myRange As Range
    Set myRange = Range ("A1: A"&n, "D1: D"&n)
    'Create array myArray
    Dim myArray As Variant
    myArray = myRange
    'Associative array definition
    Dim dicH As Object 'key: Item name Value: Height
    Set dicH = CreateObject ("Scripting.Dictionary")
    'Loop variable
    Dim i As Long
    'Key variable
    Dim wkey As Variant
    'Store with if statement
    For i = 1 To n
        wkey = myArray (i, 1)
        If dicH.exists (wkey) = False Then
            'Register height when first key appears
            dicH (wkey) = myArray (i, 3)
        Else
            'Upon subsequent key appearances, update the height with the larger one
            If dicH (wkey)


    Below is the execution result. (Sheet content is the same as your presented image)
    A 98
    B 61
    D 42

  • Answer # 2

    If the question is different from the subject, and you want to obtain the "list number that is the maximum value of each item name" described in the question text, add it to tatsu99's source. One more trick is needed.
    For example,
    1) Prepare a dictionary with the key as the item name and the value as the list number.
    2) Change the value of dicH to "list number_maximum height" and use the maximum height on the right side of _ when determining the maximum value.
    3) After extracting the maximum height, loop again and search for a line where the maximum value matches the item.
    Such.

    1) is good?

    Dim dicHNo As Object 'key: Item name Value: Add list number of maximum height
    Set dicHNo = CreateObject ("Scripting.Dictionary")
    If dicH.exists (wkey) = False Then
       dicH.add wkey, myArray (i, 3) 'Modify
       dicHNo.add wkey, i 'add
    If dicH (wkey)
    Debug.Print wkey, dicH (wkey), dicHNo (wkey)


    Output result
    A 98 2
    B 61 3
    D 42 4

  • Answer # 3

    As a suggestion of coding method, I

    Create class A with four members: maximum height, maximum height list number, maximum length, maximum length list number.

    Use a dictionary with the item name as the key and class A as the value, loop through the Array and extract the list number of the maximum height and the list of the maximum length for each item.

    If there are multiple list numbers with the same maximum value, it is necessary to devise how to extract and class A depending on whether the list number to be acquired is one or all.

Related articles