VBA get Total, Max, Min, Average of repeating dates in a column

[Ria]

Hi Folks,

I am stuck with a bit of EXCEL 2003 VBA problem. It might be easy and simple for all of you. Spent lots of time searching solution on web but could not find any code that satisfy my need. I need VBA code, so I can run macro with command button.
I get lots of text files that we open in excel and consolidate data before it is used. Excel workbook can have many sheets with separate data. Titles of each columns is this:

Col A=Date B=Total C=Max D=Min E=Average

In date column A, some of the dates repeat (more than one row of same date) and we need to consolidate/combine data in a single row till last row of data in each sheet. What I need is:
1. check Date in column A, if date does not repeat in next/lower line then copy
that row from range e.g. A7:E7, and paste it into column G7, ELSE
If date repeats in column A then:
a. copy date from Column A and paste it into Column G eg. Value of A3 into col G3
b. for this date e.g. A3:A6, Sum all values from col B3:B6, and put into col H3
c. for this date e.g. A3:A6, get maximum value from col B3:B6, and put into col I3
d. for this date e.g. A3:A6, get minimum value from col B3:B6, and put into col J3
e. for this date e.g. A3:A6, calculate/get average value from col B3:B6, and put into col K3
2. Do it till last row of data
3. Do it for each sheet starting from sheet 2
4. Output data should not have empty rows. After consolidation number of rows will be less than rows from RAW DATA e.g. sheet USO, col A has 184 row and col G:K has 168 rows.
Attached is sample excel file. in sheet USO and UNG, col A to E is raw data and
repeating dates are highlighted. col G to K is out put data that was done manually.
I am using EXCEl 2003. Hopefully someone will be out there to help.
If anything not clear, please feel free to ask.

Sorry deleted lots of data and sheets because file size was too big. There is enough data to do tricks asked above.

Thanks

Riaz

 

[NARAYANK991]

Hi ,

See if this is OK. You will need to run the macro called ConsolidateData.

Narayan

 

[Ria]

Hi ,

See if this is OK. You will need to run the macro called ConsolidateData.

Narayan

Thank you very quick response.
Code you provide does not do job well.
1. It is calculating only sum and gives wrong answer.
2. When it puts data from range A:E to new location eg G:K, it skips lots of lines.
3. Number of lines it prints in range G:K are very less
4. You inserted drop down menus in line 2 cols A:E, it looks good but we gets data in text files and each text file data goes in a separate sheet in single workbook. Adding dropdown menu is an extra work and we do want it.

I am sorry but appreciate your effort to look at it.

Anyone else has some idea how to handle it properly, please.

Thanks,
Riaz

 

[NARAYANK991]

Hi ,

Thank you for your detailed feedback.

1. This is a mistake and easily rectified. See the attached file.

2. This is also a mistake and has been rectified in the attached file.

3 and 4 is not clear to me. As far as I can see , the autofilter which you have mentioned as point 4 , has nothing to do with the code ; neither is it being introduced in the code , nor is the code dependent on it.

It is clear from your post that you are looking for someone else to help out. I am sure others will help.

There is really no need to give any further feedback on this post of mine.

Narayan

 

[Ria]

Hi ,

Thank you for your detailed feedback.

1. This is a mistake and easily rectified. See the attached file.

2. This is also a mistake and has been rectified in the attached file.

3 and 4 is not clear to me. As far as I can see , the autofilter which you have mentioned as point 4 , has nothing to do with the code ; neither is it being introduced in the code , nor is the code dependent on it.

It is clear from your post that you are looking for someone else to help out. I am sure others will help.

There is really no need to give any further feedback on this post of mine.

Narayan

Great and thanks. Now it works the way as we want.