Manipulating and populating the data from sheet X to sheet Y using dynamic VBA script

  • Hi guys, just wanted to ask for your suggestions on how to approach the 2 following issues in terms of writing the script that I need to populate data from sheet X into template within sheet Y.


    1) Multiple items divided by “;” are in column B within sheet X and need to be split once moved to template Y, but in the separate row of the same column. E.g. x;g;h; in the cell B2 (sheet X) would need to be populated into template Y as B2 for x,B3 for g, B4 +h. How should I go about it?


    2) I have a shortcut of signs in the identification section of the sheet X (lets say in column D), the values are in the following format (all in D2): (ID):1234;(IB): 321; (IC):213.

    Now I need to move the following to template Y where ID = Identification; IB = Internet barcode and IC = Identification of court (as per the created lookup table). How do I write a code that reads from look up table and populates this data into new table as new expanded/ expressive form? E.g. (ID):1234;(IB): 321; (IC):213 in sheet X -> (Identification):1234;(Internet Barcode): 321; (Identification of Court):213 in sheet Y (e.g. also column D2)


    3) This is related to issue 1..How do I copy multiple records from sheet X populating them into Y, if I want the next record to be populated into A column and the first (full) available row? Given the scenario in section 1 (i.e. according to example given above, the new record would start/be inserted in A5 as x;g;h was moved to B2-B4 in template Y).



    PLEASE NOTE THIS IS JUST EXAMPLE AND THE FINAL SCRIPT SHOULD CATER FOR 1OOs OF RECORDS.



    Thank you so much - Any guidance/help would be appreciated!

  • Hello Jolivanes!


    First of all, thank you very much for your help. I am struggling to make it work, so let me be perhaps more complete in terms of my example! also, if you could write comments in the code it would help - thank you for that.


    I need to transform a data input from the attached example as per follows and struggle to write a script that would cater for such needs. (Please see the attached).
    The data shall be copied from "original" and the columns should be split as "transformed" based on the information in the "key" sheet.
    The loop should then run row by row and next line shall be inserted into next available row as illustrated on example in attachment.
    Many thanks for your guidance - I really appreciate it!

    Regards,


    Tilly

  • Possibly...

    If I've been helpful, let me know. If I haven't, let me know that too.