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
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.
-
Answer # 1
-
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
- 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
An example using a dictionary (associative array).
I created it with n = 5.
Below is the execution result. (Sheet content is the same as your presented image)
A 98
B 61
D 42