Excel: Data collation & conversion

[gaikwad.mm@gmail.com]

Dear Experts

I' need your help for creating a Macro which will help me to avoid the human errors and efforts while collating the data from Horizontal to vertical, both merge un-merge cells as well as the values with "," comma.

please find attached the sample file where I' have mentioned the RAW data as well as the required format. I' hope that the file is understandable.

Let me know in case more details are needed.

Thanks
MG

[gaikwad.mm@gmail.com]

Dear Experts

I' hope that someone is looking for the solution which can be provided to me..... both requirements can be done with 2 different macros and not required in single macro.

[FDibbins]

At least part of this can be done with a regular formula, no need for VBA.

In C16, copied down, use this...
=INDEX($A$3:$L$9,MATCH($A16,$L$3:$L$9,0),MATCH($B16,$A$3:$L$3,0))

For the 2nd table (Master Data), why is the data in K merged like that? Also, why are there 2 rows for every "row" of data? Merging cells etc should be avoided if at all possible - it causes more problems than anything else

Also, so that members can tailor their suggestions for you, please include your excel version in your profile (looks like 2007 or later)

[gaikwad.mm@gmail.com]

Thanks for reply FDBins

The formula you have given for C16 is working properly..., But my target is to all the 3 columns and not just one for which you have shared the formula which is based on other 2 columns... it should not be like that... all 3 columns should be created by their own with shown data....

For K Column... the raw data is like that only.. as it is coming with the drop down list with multi selection. and about the merge cell... there are 2 , 4 , and sometime 6 rows merged ...

The format which I' have share cannot be changed... because there are many other things based on this format.. hence need your help to get the solution with the same format

THANKS IN ADVANCE

[watersev]

hi MG, option for Master Data, press "Master Data" button

[gaikwad.mm@gmail.com]

Hey!!! Watersev

This is fantastic..... Can you do the same for other 3 columns also exactly like shown in the required format... this will be really helpful

Please take care that the merge cell can be 2.3. or other number... also the rows will be more than shown in sample file

thanks
MG

[watersev]

please check attachment, press "Rupees and Master Data" button

[gaikwad.mm@gmail.com]

Ohhhh.... Great.... It Working As Required....

Question..

My format's columns might get increase or decrease.. how can I' modify the code based on it...

also are both macros included in single module ... or you are using both modules......


Thanks
MG

[watersev]

the code being used is in Module 2 named "Rupees_and_Master_Data". The initial code for Master Data is not used.

The code is done for the current format of the sample file. I don't know what changes might be necessary for any new format.

[gaikwad.mm@gmail.com]

Ok...

As of now this seems OK... And I' would like to thank you for all your effort to help me for getting the solution

THANKS A LOT
MG

[gaikwad.mm@gmail.com]

Dear watersev

I' have tried to apply your given macro in my original file... but due the number of columns are vary I' am not able to modify your code...

could you please help me in updating your code as per the attached format....

also request you to put some comments wherever modification are need to be done in case columns increased or moved to different location...


PLEASE HELP ME WITH IT - THANKS IN ADVANCE

MG

[gaikwad.mm@gmail.com]

Hello... Still waiting for your reply... This will be re

[watersev]

hi MG, please check attachment, run code "test"

The code should work as expected and does not need any modifications provided:
1. The data table starts from A1 and has at least 3 columns.
2. Codes column is the last column, Product column comes before the last column.

You can test it deleting and adding the columns provided the above set conditions are met.