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
- Create array of n rows x 4 columns with myArray ()
- First, create an n-row x 4-column array only when the item name is A
- Create an n-row x 1-column array with only length and depth data from the array generated in 2.
- Get the largest line number and data in 3.
I was thinking.
- The contents of myArray (n) are as follows
L myArray (n, 1)
L myArray (n, 2)
L myArray (n, 3)
L myArray (n, 4)
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.
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)
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.
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.
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)
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.
- vba - range value is batch-assigned to dynamic array by range error when the number of data is 1 or less
- python - i want to extract the maximum value each time in a list that counts consecutive values
- vba - fetch the row with the maximum value among multiple conditions
- i want to extract the character string enclosed in () with vba and overwrite it
- python 3x - how to get the row with maximum value for each group and create and mark a new column (python, pandas)
- display of maximum value and minimum value of java
- c++ - how to identify the same column from a list and extract that column or some value in excel
- i want to extract the contents of multiple excel sheets with vba and store them in sql server
- i set the maximum value in python, but when i graph it, the value exceeds the maximum value
- i want to extract multiple excel books in the directory with vba, store them in sql server, and register them
- vba i want to find the cell where the numerical value is entered and display it in the user form
- a program that finds the maximum value of each column and finds the minimum value from it
- [vba] copy value from work book to another book
- vba - enter value in combo box with selenium basic xpath
- vba - bring in a value that references a column name in a text box in an access report
- vba - extract information on a separate sheet with excel
- vba - cannot store the total value extracted by the auto filter in a variable
- vba - macro to extract rows containing specific words and output to another worksheet
- i want to not convert when the value is larger than the maximum value of integer type or smaller than the minimum value
- excel vba [i want to auto filter by the value of the list box]
- an error occurs in the application definition or object definition when inputting the stored array in vba dictionary at once
- array - i want to display the json file line by line in processing
- vba - after importing csv into a table, i want to make the data with some items deleted as a new table
- sheet creation with vba
- about n/a at the time of batch input to cells of vba one-dimensional array
- vba - i want to get multiple records in a csv file, but i can only get one record (a pair of headers and values)
- unity - about the means of writing novel games
- i want to count the number of elements in the array received from the vba api and create a conditional branch with the element i
- i don't know how to remove some lines using php: fopen ()
- how to inherit array data between modules in vba