VBA macro Code for grouping based on column A

[Dharani Suresh]

Hello everyone,
i need to group the values based on A, B, C columns.

which means all "Chickpea_B-1_11area_3d.csv " will have same timestamp and mean_area_3d values, only D column vlaue changes.....
in the same way, all "Chickpea_B-1_12area_3d.csv" will have same timestamp and mean_area_3d values, only D column vlaue changes.....

now i need all Chickpea_B-1_11area_3d.csv values to be grouped into one. and chickpea_B-1_12area_3d.csv values to be grouped into one..

if you see the output sheet, you will understand clearly, which i did manually for your easy understanding...

i did manually and also attached the sheet for your kind reference and easy understanding... where i grouped all Chickpea_B-1_11_area_3d aver_domain_area, fill_factor, leaf_angle_theta, plantHeight, projected_leaf_a into one.... where b and c cols are same for all the D E F G H I Values. in the same way for Chickpea_B-1_12_area_3d and all other below values to be grouped.

like this i have to do for 1000 files. please help me..
please find the attachment...

Thank You...

With Kind Regards,
Dharani.

[RudiS]

A Pivot Table can provide any grouping for you quickly and easily.
Have you considered trying this tool?
See attached...

[Dharani Suresh]

A Pivot Table can provide any grouping for you quickly and easily.
Have you considered trying this tool?
See attached...

Hello RudiS,
first of all my big thanks to you for such a quick reply....
yes i know about this pivot table... as you suggested me, i tried this technique.. but i am not getting my required output... i need all b1 and 1 values separately, but in this pivot table it is making all the values into one and giving output as count or sum or averages etc., but this is not my requirement...

in other words, i just need all my parameters values to get transposed against each sector and trench.... which i did manually in the attached excel sheet... please refer that...

I think only macros can do this..
looking forward for the positive reply...

Thank you,
With Regards,
Dharani.

[RudiS]

I'm sorry Dharani,

I must be missing the point as I do not understand how you get the values in your sample sheet called "Required Output"

If I look at your Input sheet I see headings:
sectors--- Timestamp--- mean_area_3d--- sec_0_area_3d

I can understand that you want the *.csv files grouped, and sub grouped by TimeStamp, but wher does the mean_area_3d column apply as they are all different and will not create groups within the previous two columns. Also in your output sheet, where does the data in columns E through to I come from?

I need more clarity please.

[Dharani Suresh]

I'm sorry Dharani,

I must be missing the point as I do not understand how you get the values in your sample sheet called "Required Output"

If I look at your Input sheet I see headings:
sectors--- Timestamp--- mean_area_3d--- sec_0_area_3d

I can understand that you want the *.csv files grouped, and sub grouped by TimeStamp, but wher does the mean_area_3d column apply as they are all different and will not create groups within the previous two columns. Also in your output sheet, where does the data in columns E through to I come from?

I need more clarity please.

Oh that's OK RudiS...
you are trying to help me that's really great and appreciable...

for E to I values:
in output sheet: the col E comes from the input sheet from F col.
F to K in output sheet comes from G col in the input sheet...

I am attaching you the revised one excel file with this reply... please refer that excel sheet... it will be easy for you to understand...
looking forward for the positive reply...

Thank you...

With Regards,
Dharani.

[RudiS]

TX. Now I understand.
I'll see what I can do.......

[Dharani Suresh]

TX. Now I understand.
I'll see what I can do.......

You are most welcome RudiS...
looking forward for your positive reply as early as possible...

Thank you,

With Kind Regards,
Dharani.

[RudiS]

Hi Dharani,

I had to turn this project over to a friend of mine from another forum at: http://www.eileenslounge.com/portal.php, as the complexity was a bit much for my less experienced mind. HansV is a Microsoft MVP and an excellent VBA programmer who was kind enough to take time out and create this code for your workbook. You can find the post I placed here: http://www.eileenslounge.com/viewtop...unread#p124862, which includes his reply to a simplified example. I took his code and modified it slightly to fit the context of your workbook.

Attached is the working macro that transposes the data as required....
Thank you Hans!

[Dharani Suresh]

Hi Dharani,

I had to turn this project over to a friend of mine from another forum at: http://www.eileenslounge.com/portal.php, as the complexity was a bit much for my less experienced mind. HansV is a Microsoft MVP and an excellent VBA programmer who was kind enough to take time out and create this code for your workbook. You can find the post I placed here: http://www.eileenslounge.com/viewtop...unread#p124862, which includes his reply to a simplified example. I took his code and modified it slightly to fit the context of your workbook.

Attached is the working macro that transposes the data as required....
Thank you Hans!

Hi RudiS,

OH MY GOD…. You are really great… My heartfelt thanks to you Rudi…. You took really a very painful effort in helping me…
In the public forums, this is the first time I am seeing a person like you helping to this extent … you tried a lot and also you have asked your friend… thank you so much Rudi for helping me like this… I just don’t have words to express my gratitude towards you…

Special thanks to your friend, HansV who also helped me in solving my problem…. A bunch of thanks to him… he is really kind and awesome…
MY BIG BIG THANKS TO BOTH OF YOU PEOPLE….

A small error:
Rudi your code is working perfectly in the file which you gave me called grouping, where there is one button called transpose parameters. But if I copy and paste the code in some other excel file, it is giving the output in some other different formats.. I have tried that code in one of my other excel sheets and I have attached with this thread for your kind reference to see that different outputs… but if I copy and paste my data in your grouping file, its giving the correct output…

My question is why the code is not giving correct output if I copy and paste the code in another file (input format is same)? its doing some magic, every time when i run in different excel sheet, first time some different answer, second time some different answer, third time giving the correct output..
what is the reason for this???? i am very much curious to know about this????
Please guide me, what should I do now???

Thank you…
With Kind Regards,
Dharani.

[RudiS]

Hi,

Its just a small consistency issue.

In your first file the order of the columns was like this: 1.jpg
Since Plant, Trench, Sectors,Timestamp and Mean Area are all static, I kept them on the left and moved Parameter to the right, next to Sec_0. This makes it easier for the macro to process.
Notice the code to move the Parameter (D column) to the G column position: 3.jpg

In the second file the order is different: 2.jpg
So now what happens when you run the macro is that it moves the Mean_area into the G column position and creates column headings from that.

I'd recommend that before you run the macro on different files, first ensure the column order is as follows:
Plant, Trench, Sectors, Parameter, Timestamp, Mean_area_3d, Sec_0_area_3d

Hope this is clear...

[Dharani Suresh]

Hi,

Its just a small consistency issue.

In your first file the order of the columns was like this: Attachment 315615
Since Plant, Trench, Sectors,Timestamp and Mean Area are all static, I kept them on the left and moved Parameter to the right, next to Sec_0. This makes it easier for the macro to process.
Notice the code to move the Parameter (D column) to the G column position: Attachment 315617

In the second file the order is different: Attachment 315618
So now what happens when you run the macro is that it moves the Mean_area into the G column position and creates column headings from that.

I'd recommend that before you run the macro on different files, first ensure the column order is as follows:
Plant, Trench, Sectors, Parameter, Timestamp, Mean_area_3d, Sec_0_area_3d

Hope this is clear...

Hello RudiS,
ya, you are right, i didn't notice this... sorry...
I will follow your format... then in this case, the code will work perfectly...

Finally, my big thanks to you RudiS... You are so kind and humble... My heartfelt thanks to you RudiS for taking such painful efforts and helping me...
you are simply GREAT... I don't know how to express my thankfulness and tribute to you...

Thank you so much, thanks a lot for spending your most valuable time for helping me.. I hope, in case i have any other future query like this, you will be helping me... kindly i request this from you...

Thank you...
Please convey my thanks to your wonderful friend also, HanS...

With Kind Kind Regards,
Dharani.

[RudiS]

Thanks for your feedback and kind words Dharani. It was a pleasure to assist.

This forum is full of helpful people who freely offer up their time and expertise to assist members with questions and solutions. It just happened that I responded first to your query. I am sure that if I am not around for your next question, there will be plenty of others who will be able to provide a solution for you. I don't have doubt in that. It's the joys of belonging to a forum such as this one which has quality people with exceptional skills...

Cheers

[Dharani Suresh]

Thanks for your feedback and kind words Dharani. It was a pleasure to assist.

This forum is full of helpful people who freely offer up their time and expertise to assist members with questions and solutions. It just happened that I responded first to your query. I am sure that if I am not around for your next question, there will be plenty of others who will be able to provide a solution for you. I don't have doubt in that. It's the joys of belonging to a forum such as this one which has quality people with exceptional skills...

Cheers

Thank you RudiS... ya ya you are right... this forum experts are really great and helpful... i really wonder and admire the people in the forums who are all very much great, awesome, ........ and ready to help others in the time of their requirement...

Hats off to the people in the forums who help others by spending their most valuable time... Thank you experts....

A bunch of million of Thanks to you RudiS... i was able to accomplish my work successfully with your great great help...

With Kind Regards,
Dharani.

P.S., Now i am closing this thread... thank you all who helped me to solve my problem....