Macro to Pickup a unique values in a column and sum up the value against it in all lines.

Dear Macro Gurus,

I'm trying to explore some fixed length file and extract data based on certain key conditions. I managed to get the required information from the flat file. But now i need to group it and make another report out of it.!

Here is my data,

File Name Debit Amount Credit Amount
CPUA0001.079 ----- 11412.38
CPUA0001.076 ------ 1202.19
CPUA0001.076 1202.19
CPUA0001.076 1202.19
CPUA0001.076 1202.19

Now I need to group these against the file name and get the total debit and total Credit. Something like below in a seperate sheet.

Unique Filename Total Debit amount Total Credit Amount.
CPUA0001.079 XXXXX XXXXX
CPUA0001.076 XXXXX XXXXX

Please help me to achieve this! Thanks a lot.
Do let me know if you require any other info

If your data is sorted (so all unique filenames are grouped together you can do something like:

Please Login or Register  to view this content.

You could also do this using formulas rather than vba with the sumif function.

You could also do this using formulas rather than vba with the sumif function AND PIVOT TABLE

I think sum if can be used only if you need to calculate organised data, here the amount value is appearing in the multiple columns and in multiple rows, since a file can contain multiple credits and debits.
I need to sum it based on a unique file.! its challenging for me!

I'm not even able to group it with the unique values.
Here i need to group / pull out the unique files and all its amount ( including credit and debit ) then I can sum up it easily.

Originally Posted by yudlugar

If your data is sorted (so all unique filenames are grouped together you can do something like:

Please Login or Register  to view this content.

You could also do this using formulas rather than vba with the sumif function.

I'm not even able to group it with the unique values.
Here i need to group / pull out the unique files and all its amount ( including credit and debit ) then I can sum up it easily.

Well you could either use sort before you run the macro or include the sort in the macro:

Please Login or Register  to view this content.