Need to sum up numerous columns in different worksheet into 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I generate 100-200s .csv files end of each month for my company.

Each of these .csv files have numerous data that I need to sum up for a
report.
(Example: 1.csv have 3 columns with things like, item A, item C, item D etc
and 2.csv have columns with item B, item C and item E... and so on and so on)

I am required to sum up all these 100-200s .csv files and have a total sum
of how many of each items appeared and used by the users.
(Example: item A - total count 100, item B - total count 20.. .etc etc)

Is there a way I could do it without going through each of the .csv files
and counting them manually?

The number of columns and the number of name of the items (the same name for
each item are in used) are not fix in each .csv files.

Could anyone please help me with a solution?


Regards,
Lee
 
Lee,

Post two small data tables as examples, and a third showing what you would
like to have as the result of the combination. I just wrote a macro that
would combine
File1:
Data1 Data2 Data3
1 2 3
4 5 6

File2:
Data3 Data4 Data5
7 8 9
10 11 12

into:

Source Data1 Data2 Data3 Data4 Data5
File1 1 2 3
File1 4 5 6
File2 7 8 9
File2 10 11 12


If that is what you are looking for, post again.

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

Actually I am looking for something like this instead;

example as below,

File1:
Item A Item B Item C

File2:
Item A Item C Item D Item E

File3:
Item A Item B Item D Item E Item F

Into:

Source Item A Item B Item C Item D Item E Item F
Sum 3 2 2 2 2
1

This is what I am looking for.

Thank you.

Regards,
Lee
 
Lee,

If you actually have .csv files, then you could use something like the macro
below. Put the code into a codemodule of a new workbook, save the workbook,
then run the macro. Select the CSV files when prompted. This will work as
long as you have fewer than 254 unique items.

HTH,
Bernie
MS Excel MVP

Sub CountFromSelectedFiles()
Dim filearray As Variant
Dim myBook As Workbook
Dim myCell As Range
Dim i As Integer

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

filearray = Application.GetOpenFilename(MultiSelect:=True)

ThisWorkbook.Worksheets(1).Cells.Clear

If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Set myBook = Workbooks.Open(filearray(i))
For Each myCell In ActiveSheet.UsedRange
If myCell.Value <> "" Then
If Not (IsError(Application.Match(myCell.Value, _
ThisWorkbook.Worksheets(1).Range("1:1"), False))) Then
With ThisWorkbook.Worksheets(1).Cells(2, _
Application.Match(myCell.Value, _
ThisWorkbook.Worksheets(1).Range("1:1"), False))
.Value = .Value + 1
End With
Else
With ThisWorkbook.Worksheets(1).Range("IV1"). _
End(xlToLeft)(1, 2)
.Value = myCell.Value
.Offset(1, 0).Value = 1
End With
End If
End If
Next myCell
myBook.Close False
Next i
End If

ThisWorkbook.Save

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
 
Back
Top