Need to sum up numerous columns in different worksheet into 1
 

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


"devil135" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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


"devil135" wrote in message
...
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,

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



"devil135" wrote in message
...
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

"Bernie Deitrick" wrote:

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


"devil135" wrote in message
...
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