Home>
You are trying to uniquely aggregate duplicate information in VBA.
All you need is the summary result, and you don't want the formula to remain in the cell.
Currently, the calculation formula is entered once as follows and then converted into text.
For this reason, I feel it was useless to write to the same range twice.
[Data duplicated! A: A] .Copy [a1]
[a: a] .RemoveDuplicates
Range (Cells (Rows.Count, 1) .End (xlUp) .Offset (, 4), [b1]) = Array (_
"= VLOOKUP (A1, duplicate data! A: B, 2, FALSE)", _
"= SUMIF (Duplicate data! A: A, A1, Duplicate data! C: C)", _
"= SUMIF (Duplicate data! A: A, A1, Duplicate data! D: d)", _
"= VLOOKUP (A1, Data duplicated! A: e, 5, FALSE)" _
)
[a1] .CurrentRegion = [a1] .CurrentRegion.ValueIf the formula is only one column
[Data duplicated! A: A] .Copy [A1]
[a: a] .RemoveDuplicates
Calculation result = WorksheetFunction.VLookup (Range ([A1], [A1] .End (xlDown)), [Duplicate data! A: B], 2, False)
Range ([b1], [A1] .End (xlDown) .Offset (, 1)) = calculation result, but
If i have multiple columns, you can't make an array to put the result well (reserve the size and put in for?),
On the contrary, I think it will be late.
If i have a good way to solve this, please let me know.
-
Answer # 1
Related articles
- unable to copy vba autofilter results
- vba - [excel] i want to calculate the lowest price in 3 days on another sheet
- vba - i want to copy the results filtered by the filter to the cells of another sheet, one cell at a time
- vba - i want to list the search results in another subform
- i want to get multiple character recognition results from inkpicture drawing with vba
- i want to connect multiple sql results with vba
- vba - there is a difference in macro results before and after initializing the sheet
- vba - i want to display the count results every 10 minutes, 30 minutes, and 1 hour based on the time in the table
- excel vba: we want to export "test results" for 20 people into a continuous pdf format and send an email to each
Trends
Not an answer. . .
I thought that the idea was wonderful because it was processed concisely.
On the other hand, using VBA and relying on the actual formula on the worksheet side is not very good (the role assignment is not clear).
If you want to do it without using mathematical formulas, prepare an array of number of rows x number of columns (4), throw the calculation result into it, and finally assign it to the target range.
The difference in speed depends on the number of lines, and you will not know unless you actually try it.
Could you make it while asking questions like this? I think.
The conclusion depends on what the questioner wants to prioritize.
We want to prioritize the processing like VBA
→ Replace with an implementation that does not use mathematical formulas
We want to give priority to simplicity as processing
→ Current status
We want to prioritize processing speed
→ Create an array (or another method) and compare the processing speed