StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel VBA merge csv and sum values
text
Body
copied!<p>I have a need to merge a list of reports in the following format</p> <ul> <li>Column 1 is a number </li> <li>Column 2 is a string </li> <li>Max rows is dynamic</li> </ul> <p>In such a fashion as to sum the numbers in column 1 for strings that match (removing duplicates)</p> <p>Currently I am merging the csv files with this</p> <pre><code>Dim files() As String files = AllFiles(GetFolder) 'returns array of files in folder Dim i As Long For i = 0 To UBound(files) Windows("Monthly KB Page Access Report Generator.xls").Activate ActiveCell.Value = files(i) wrkbookvar2 = Dir(files(i)) ActiveCell.Offset(1, 0).Activate Workbooks.Open Filename:=GetFolder & "\" & files(i) Set dataset_workbook = ActiveWorkbook Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy Windows("Monthly KB Page Access Report Generator.xls").Activate Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select ActiveSheet.Paste dataset_workbook.Close Next </code></pre> <p>This merges with 2 issues</p> <ol> <li>First cell of each report is overwritten with the reports filename</li> <li>After opening each file it prompts the user saying the clipboard has large data in it, I want to avoid this</li> </ol> <p>I think the following code might work to merge my data and sum values but I cannot be sure because I am currently type missmatching due to the reports filename showing up</p> <pre><code>' Sort Windows("filename.xls").Activate Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending ' merge LastRow = ActiveSheet.UsedRange.Rows.Count Set r = ActiveSheet.UsedRange.Resize(1) With Application.WorksheetFunction For iRow = 2 To LastRow If Cells(iRow, 2) = Cells(iRow + 1, 2) Then Cells(iRow, 1) = Cells(iRow, 1) + Cells(iRow + 1, 1) Rows(iRow + 1).Delete End If Next iRow End With </code></pre> <p>Anybody mind helping me iron this out, I am terrible at VBA.</p> <p>UPDATE: Number 1 was resolved by removing </p> <pre><code>Windows("Monthly KB Page Access Report Generator.xls").Activate ActiveCell.Value = files(i) wrkbookvar2 = Dir(files(i)) ActiveCell.Offset(1, 0).Activate </code></pre> <p>They were part of an older copy that I completely didnt notice.</p> <p>Everything is working except I need to adjust my 'Merge to account for more then 2 instances of a value, right now it will only merge 2.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload